Удалить из массива элементы

Просьба помочь это сделать одним запросом

Вынеси contacts в отдельную таблицу, сделай таблицу связи docs_contacts(doc_id, contact_id), потом удаляй из этой таблицы просто нужные строки.

Можно и без отдельной таблицы contacts обойтись, но в реальном мире скорее всего она пригодится.

Дело в том, что такая таблица была, но в ней очень, очень много записей, поэтому сделана таблица docs и в ней специально сгруппированы контакты в массивы.

В вашей прошлой теме буквально и дословно это делали. Что не устроило в том варианте?

Плюсую нормализацию таблицы.

Да, никогда не пиши СУБД-специфичный код, даже автоикремент не употребляй - триггеры есть везде. Делай свои SQL-запросы максимально переносимыми.

Задачу невозможно решить одним запросом. Для удаления контакта из массивов нужен запрос типа update. Для удаления документов с пустым массивом контактов нужен запрос типа delete. Разве это не очевидно?

Почему это не объединить в один запрос? Например использовать cte для update

CTE (comon table expression) это select. insert, update и delete позволяют использовать cte, это позволяет комбинировать их с select: select+insert, select+update, select+delete. Но как ты собираешься использовать cte для комбинирования update+delete?

использовать cte для комбинирования update+delete?

Может вы поясните, чем вот то, что я писал плохо?

WITH cte_user AS (
	SELECT DISTINCT UNNEST(ARRAY['27672','6145']) AS "Id"
)
,cte_sel AS (
	SELECT
		 d."@Id" 
		,d."Account" 
		,(
			SELECT array_agg("Id") 
			FROM UNNEST(d."User") ou("Id")
			WHERE NOT EXISTS (SELECT FROM  cte_user cu WHERE cu."Id" = ou."Id")
		) AS "NewUser"
	FROM
		"Documents" d
	WHERE 
		d."Account" = 4777912
)
,cte_upd AS (
	UPDATE "Documents" d
	SET "User" = s."NewUser"
	FROM cte_sel s
	WHERE
		d."@Id" = s."@Id"
		AND s."NewUser" IS NOT NULL
)
DELETE FROM "Documents" d
USING cte_sel s
WHERE
	d."@Id" = s."@Id"
	AND s."NewUser" IS NULL

Во первых, такой запрос вредит ментальному здоровью разработчика (большинство, к сожалению, даже не смогут его прочитать). Во вторых, он плохо читается и содержит сложные условия. В третьих, нормализация данных позволяет избежать первых двух проблем.

Любую задачу можно решить сколько угодно изощренным способом и это всегда вдет к ухудшению системы. У человека явно не задачка из учебника как задрочиться в SQL, а практическая в работающей системе.

А почему для вас проблема что в таблице много записей? Ведь очевидно что проблема была не в занимаемом месте (т.к. в таком виде места жрет куда больше).

специально сгруппированы контакты в массивы.

чтобы потом стоя в гамаке вприсядку?

не задачка из учебника как задрочиться в SQL, а практическая в работающей системе

Я вас уверяю, практические задачи в работающей системе, которую до тебя разрабатывали с десяток студентов, или даже с десяток Гуру (но каждый - со своим видением) - ни одному учебнику не снились по «задрочится так, чтобы всё работало как раньше, останавливать нельзя, менять структуру нельзя».

Да, никогда не пиши СУБД-специфичный код, даже автоикремент не употребляй - триггеры есть везде. Делай свои SQL-запросы максимально переносимыми.

Зачем? У него и так postgres уже.

Это здесь и сейчас postgres, а ну сменят СУБД на другую? Или место работы сменит или еще что. А еще бывает, что поведение sql-СУБД специфического кода при смене версии ломается, сам видел. Поэтому скрипт должен быть максимально логически простым.

Конечно. Третья нормальная форма? Не, не слышали.

Спасибо, проверю ваш вариант, думал что можно проще написать запрос.

Предполагал, что можно найти разность текстовых массивов, по аналогии с числовыми массивами типа

select array[1,2,3] - array[2,3] = array[1]

Но к сожаление это не работает для строк.

Планировал:

1. обновить в cte массивы, как разность двух массивов

2. удалить записи с пустыми массивами

Использовать РСУБД, чтобы пихать в поля текст и изобретать велосипеды для формализации… Это какая-то спецолимпиада?

Советую использовать 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-религиозников, которым в голову вбили что нормализация это единственный верный путь, а оценить плюсы других решений у них мозгов не хватает.

Ты другие его темы глянь.

В каком «таком»? Нормализация всегда жрёт а разы больше места, потому что вместо того чтобы просто хранить элемент массива вы храните заголовок тупла + id + дохлые туплы до прихода вакума + индекс, что для достаточно коротких строк как-то имена, или вообще id’шников, будет где-то на порядок больше. Плюс оверхед на лукапы и изменение этой помойки, вместо просто записи оригинальной строки с массивом.

Нормализация всегда жрёт а разы больше места

ога. так это ты место экономишь?

оба запроса читабельны и эффективны

бенчи будут?

Я был не в курсе, что cte в postgres могут содержать модифицирующие запросы.

Это здесь и сейчас postgres

Вот и славно же.

а ну сменят СУБД на другую?

Зачем менять постгрес на что-то?

Какие-то надуманные аргументы.

Это здесь и сейчас postgres, а ну сменят СУБД на другую?

Ни разу не приходилось во всех сколько-нибудь крупных проектах внезапно переходить с Postgres на другую СУБД. Вероятность того, что в ближайшем будущем мне предстоит этим же заниматься, крайне мала.

Или место работы сменит или еще что

…и что?

Делай свои SQL-запросы максимально переносимыми.

Тут тебе предлагают решать проблемы, которых нет.

Я вижу, что ТС городит херню с массивами вместо нормализации таблицы. Это плохо.

Мем - ты мне втираешь какую то дичь.

У ж мне то рассказывать не стоит. С таким говном работал и не раз. Но тут не видно всей картины и не было сказано об ограничениях такого рода.