Postgresql — чистый способ вставки записей, если они не существуют, обновить, если они есть

Хорошо, мне удалось создать тестовый пример. В результате часть обновления выполняется всегда, даже при новой вставке. COPY, кажется, обходит систему правил. [Для ясности я поместил это в отдельный ответ]

DROP TABLE pages CASCADE;
CREATE TABLE pages
    ( url VARCHAR NOT NULL  PRIMARY KEY
    , html VARCHAR
    , last TIMESTAMP
    );

INSERT INTO pages(url,html,last) VALUES ('www.example.com://page1' , 'meuk1' , '2001-09-18 23:30:00'::timestamp );

CREATE RULE Pages_Upsert AS ON INSERT TO pages
  WHERE EXISTS (SELECT 1 from pages P where NEW.url = P.url)
     DO INSTEAD (
     UPDATE pages SET html=new.html , last = NOW() WHERE url = NEW.url
    );

INSERT INTO pages(url,html,last) VALUES ('www.example.com://page2' , 'meuk2' , '2002-09-18 23:30:00':: timestamp );
INSERT INTO pages(url,html,last) VALUES ('www.example.com://page3' , 'meuk3' , '2003-09-18 23:30:00':: timestamp );

INSERT INTO pages(url,html,last) SELECT pp.url || '/added'::text, pp.html || '.html'::text , pp.last + interval '20 years' FROM pages pp;

COPY pages(url,html,last) FROM STDIN;
www.example.com://pageX     stdin   2000-09-18 23:30:00
\.

SELECT * FROM pages;

Результат:

              url              |    html    |            last            
-------------------------------+------------+----------------------------
 www.example.com://page1       | meuk1      | 2001-09-18 23:30:00
 www.example.com://page2       | meuk2      | 2011-09-18 23:48:30.775373
 www.example.com://page3       | meuk3      | 2011-09-18 23:48:30.783758
 www.example.com://page1/added | meuk1.html | 2011-09-18 23:48:30.792097
 www.example.com://page2/added | meuk2.html | 2011-09-18 23:48:30.792097
 www.example.com://page3/added | meuk3.html | 2011-09-18 23:48:30.792097
 www.example.com://pageX       | stdin      | 2000-09-18 23:30:00
 (7 rows)

ОБНОВЛЕНИЕ: Просто чтобы доказать, что это можно сделать:

INSERT INTO pages(url,html,last) VALUES ('www.example.com://page1' , 'meuk1' , '2001-09-18 23:30:00'::timestamp );
CREATE VIEW vpages AS (SELECT * from pages);

CREATE RULE Pages_Upsert AS ON INSERT TO vpages
  DO INSTEAD (
     UPDATE pages p0
     SET html=NEW.html , last = NOW() WHERE p0.url = NEW.url
    ;
     INSERT INTO pages (url,html,last)
    SELECT NEW.url, NEW.html, NEW.last
        WHERE NOT EXISTS ( SELECT * FROM pages p1 WHERE p1.url = NEW.url)
    );

CREATE RULE Pages_Indate AS ON UPDATE TO vpages
  DO INSTEAD (
     INSERT INTO pages (url,html,last)
    SELECT NEW.url, NEW.html, NEW.last
        WHERE NOT EXISTS ( SELECT * FROM pages p1 WHERE p1.url = OLD.url)
        ;
     UPDATE pages p0
     SET html=NEW.html , last = NEW.last WHERE p0.url = NEW.url
        ;
    );

INSERT INTO vpages(url,html,last) VALUES ('www.example.com://page2' , 'meuk2' , '2002-09-18 23:30:00':: timestamp );
INSERT INTO vpages(url,html,last) VALUES ('www.example.com://page3' , 'meuk3' , '2003-09-18 23:30:00':: timestamp );

INSERT INTO vpages(url,html,last) SELECT pp.url || '/added'::text, pp.html || '.html'::text , pp.last + interval '20 years' FROM vpages pp;
UPDATE vpages SET last = last + interval '-10 years' WHERE url = 'www.example.com://page1' ;

-- Copy does NOT work on views
-- COPY vpages(url,html,last) FROM STDIN;
-- www.example.com://pageX    stdin    2000-09-18 23:30:00
-- \.

SELECT * FROM vpages;

Результат:

INSERT 0 1
INSERT 0 1
INSERT 0 3
UPDATE 1
              url              |    html    |        last         
-------------------------------+------------+---------------------
 www.example.com://page2       | meuk2      | 2002-09-18 23:30:00
 www.example.com://page3       | meuk3      | 2003-09-18 23:30:00
 www.example.com://page1/added | meuk1.html | 2021-09-18 23:30:00
 www.example.com://page2/added | meuk2.html | 2022-09-18 23:30:00
 www.example.com://page3/added | meuk3.html | 2023-09-18 23:30:00
 www.example.com://page1       | meuk1      | 1991-09-18 23:30:00
(6 rows)

Представление необходимо для предотвращения рекурсии системы перезаписи. Построение правила DELETE предоставляется читателю в качестве упражнения.

Несколько хороших замечаний от того, кто должен знать это или быть очень рядом с кем-то вроде этого ;-)

Для чего нужны ПРАВИЛА PostgreSQL?

Короткий рассказ:

• Хорошо ли правила работают с SERIAL и BIGSERIAL ?
• Хорошо ли правила работают с пунктами RETURNING в INSERT и UPDATE ?
• Хорошо ли правила работают с такими вещами, как random()?

Все эти вещи сводятся к тому факту, что система правил не управляется строками, а преобразует ваши утверждения так, как вы даже не представляете.

Сделайте себе и своим товарищам по команде одолжение и перестаньте использовать роли для подобных вещей.

Правка. Ваша проблема хорошо обсуждается в сообществе PostgreSQL. Ключевые слова для поиска: MERGE, UPSERT.

Я не знаю, становится ли это слишком субъективным, но я думаю о вашем решении так: все дело в семантике. Когда я делаю вставку, я ожидаю вставки, а не какой-то причудливой логики, которая, может быть, делает вставку, а может и нет. Действительно, для этого и нужны функции.

Сначала я бы попробовал проверить URL-адрес в вашей программе, а затем выбрать, вставлять или обновлять. Если бы это оказалось слишком медленным, я бы использовал функцию. Если вы назовете его как insert_or_update_url, вы автоматически получите некоторую документацию бесплатно. Правило перезаписи требует от вас некоторых неявных знаний, а я обычно стараюсь этого избегать.

С положительной стороны: если кто-то скопирует данные, но забудет правила и функции, ваше решение может сломаться незаметно (но это может зависеть от других ограничений), но отсутствующая функция с грохотом рухнет. Не поймите меня неправильно, я думаю, что ваше решение очень креативное и умное. Как-то слишком неясно на мой вкус.

Существует пример реализации upsert / объединить с помощью простой функции в документации Postgres.

Никогда не используйте правила, они злые.

Вы не можете ссылаться на другие таблицы, кроме старых и новых, в квалификации правила. Вместо этого вы должны сделать это в теле правила. Это все потому, что правило — это всего лишь способ сообщить системе перезаписи о том, какие преобразования она должна и не должна выполнять. Правила не являются триггерами, выполняемыми для каждой строки, но они дают планировщику запросов хороший массаж и вежливо просят его переписать план. Из документов:

Что такое квалификация правила? Это ограничение, которое указывает, когда следует выполнять действия правила, а когда нет. Эта квалификация может ссылаться только на псевдоотношения NEW и/или OLD, которые в основном представляют отношение, которое было задано как объект (но с особым значением).