Add ON CONFLICT DO RETURN clause

Поиск
Список
Период
Сортировка
От Wolfgang Walther
Тема Add ON CONFLICT DO RETURN clause
Дата
Msg-id ec5d2714-71cd-3e37-8698-7b9d9b09d87d@technowledgy.de
обсуждение исходный текст
Ответы Re: Add ON CONFLICT DO RETURN clause
Список pgsql-hackers
When using ON CONFLICT DO NOTHING together with RETURNING, the 
conflicted rows are not returned. Sometimes, this would be useful 
though, for example when generated columns or default values are in play:

CREATE TABLE x (
   id INT PRIMARY KEY,
   created_at TIMESTAMPTZ DEFAULT CURRENT_TIMEMSTAMP
);

To get the created_at timestamp for a certain id **and** at the same 
time create this id in case it does not exist, yet, I can currently do:

INSERT INTO x (id) VALUES (1)
   ON CONFLICT DO UPDATE
   SET id=EXCLUDED.id
   RETURNING created_at;

However that will result in a useless UPDATE of the row.

I could probably add a trigger to prevent the UPDATE in that case. Or I 
could do something in a CTE. Or in multiple statements in plpgsql - this 
is what I currently do in application code.

The attached patch adds a DO RETURN clause to be able to do this:

INSERT INTO x (id) VALUES (1)
   ON CONFLICT DO RETURN
   RETURNING created_at;

Much simpler. This will either insert or do nothing - but in both cases 
return a row.

Thoughts?

Best

Wolfgang
Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: tweak to a few index tests to hits ambuildempty() routine.
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: tweak to a few index tests to hits ambuildempty() routine.