История изменений
Исправление slovazap, (текущая версия) :
Советую использовать jsonb вместо [], с ним многие вещи становятся проще.
CREATE TABLE docs (
id int PRIMARY KEY,
account bigint,
contact jsonb
);
INSERT INTO docs VALUES(1, 22, '["Иван", "Петр"]');
INSERT INTO docs VALUES(2, 22, '["Иван"]');
INSERT INTO docs VALUES(3, 22, '["Иван", "Виктор"]');
INSERT INTO docs values(4, 22, '["Иван", "Роман", "Сергей"]');
Вот так одним запросом, без лишних троганий записей которые не нужно менять и без повторений.
WITH changed_contacts AS (
SELECT
id,
new_contact,
new_contact = '[]'::jsonb AS should_remove
FROM (
SELECT
id,
contact AS old_contact,
contact - ARRAY['Иван', 'Петр'] AS new_contact
FROM docs
) AS tmp
WHERE new_contact != old_contact
),
deletions AS (
DELETE FROM docs
WHERE id IN (
SELECT id FROM changed_contacts WHERE should_remove
)
)
UPDATE docs
SET contact = new_contact
FROM changed_contacts
WHERE docs.id = changed_contacts.id AND NOT should_remove;
Вот так короче, но менее красиво: с повторением входного массива, хаком для CREATE TABLE AS которому нужен SELECT и в 2 запроса (потому что в CTE нельзя менять строки из разных запросов):
BEGIN;
CREATE TEMPORARY TABLE updated ON COMMIT DROP AS
WITH tmp AS (
UPDATE docs
SET
contact = contact - ARRAY['Иван', 'Петр']
WHERE contact != contact - ARRAY['Иван', 'Петр']
RETURNING id, contact = '[]'::jsonb AS should_remove
)
SELECT * FROM tmp;
DELETE FROM docs WHERE id IN (SELECT id FROM updated WHERE should_remove);
COMMIT;
Можно адаптировать это под text[] либо конвертацией в/из jsonb налету, либо этими уродставми с array_agg/unnest, если вам они нравятся.
А ещё скорее всего с новым MERGE можно написать первый запрос ещё короче и чище.
ИМХО оба запроса читабельны и эффективны. Не слушайте больных на голову SQL-религиозников, которым в голову вбили что нормализация это единственный верный путь, а оценить плюсы других решений у них мозгов не хватает.
Исходная версия slovazap, :
Советую использовать jsonb вместо [], с ним многие вещи становятся проще.
CREATE TABLE docs (
id int PRIMARY KEY,
account bigint,
contact jsonb
);
INSERT INTO docs VALUES(1, 22, '["Иван", "Петр"]');
INSERT INTO docs VALUES(2, 22, '["Иван"]');
INSERT INTO docs VALUES(3, 22, '["Иван", "Виктор"]');
INSERT INTO docs values(4, 22, '["Иван", "Роман", "Сергей"]');
Вот так одним запросом, без лишних троганий записей которые не нужно менять и без повторений.
WITH changed_contacts AS (
SELECT
id,
new_contact,
new_contact = '[]'::jsonb AS should_remove
FROM (
SELECT
id,
contact AS old_contact,
contact - ARRAY['Иван', 'Петр'] AS new_contact
FROM docs
) AS tmp
WHERE new_contact != old_contact
),
deletions AS (
DELETE FROM docs
WHERE id IN (
SELECT id FROM changed_contacts WHERE should_remove
)
)
UPDATE docs
SET contact = new_contact
FROM changed_contacts
WHERE docs.id = changed_contacts.id AND NOT should_remove;
Вот так короче, но менее красиво: с повторением входного массива, хаком для CREATE TABLE AS которому нужен SELECT и в 2 запроса (потому что в CTE нельзя менять строки из разных запросов):
BEGIN;
CREATE TEMPORARY TABLE updated ON COMMIT DROP AS
WITH tmp AS (
UPDATE docs
SET
contact = contact - ARRAY['Иван', 'Петр']
WHERE contact != contact - ARRAY['Иван', 'Петр']
RETURNING id, contact = '[]'::jsonb AS should_remove
)
SELECT * FROM tmp;
DELETE FROM docs WHERE id IN (SELECT id FROM updated WHERE should_remove);
COMMIT;
Можно адаптировать это под text[] либо конвертацией в/из jsonb налету, либо этими уродставми с array_agg/unnest, если вам они нравятся.
ИМХО оба запроса читабельны и эффективны. Не слушайте больных на голову SQL-религиозников, которым в голову вбили что нормализация это единственный верный путь, а оценить плюсы других решений у них мозгов не хватает.