LINUX.ORG.RU

INSERT ... ON CONFLICT DO UPDATE ... RETURNING id

 , , , ,


0

1
=# create table animals (
(# id bigserial primary key,
(# name text not null unique,
(# translated_name text not null default '',
(# locked boolean not null default false
(# );
CREATE TABLE
=# insert into animals (name) values ('dog');
INSERT 0 1
=# insert into animals (name) values ('dog');
ERROR:  duplicate key value violates unique constraint "animals_name_key"
DETAIL:  Key (name)=(dog) already exists.
=# insert into animals (name,translated_name) values ('dog','собака');
ERROR:  duplicate key value violates unique constraint "animals_name_key"
DETAIL:  Key (name)=(dog) already exists.
=# insert into animals (name,translated_name) values ('dog','собака')
on conflict do update set translated_name = 'кот' where locked = false returning id;
ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 1: ...ls (name,translated_name) values ('dog','собака') on conflic...
                                                             ^
HINT:  For example, ON CONFLICT (column_name).
=# insert into animals as a (name,translated_name) values ('dog','собака') on conflict (name) do update set translated_name = 'кот' where a.locked = false returning id;
 id 
----
  1
(1 row)

INSERT 0 1
=# insert into animals as a (name,translated_name) values ('dog','кот') on conflict (name) do update set translated_name = 'кот' where a.locked = false returning id;
 id 
----
  1
(1 row)

INSERT 0 1
=# update animals set locked = true where id = 1;
UPDATE 1
=# insert into animals as a (name,translated_name) values ('dog','кот') on conflict (name) do update set translated_name = 'кот' where a.locked = false returning id;
 id 
----
(0 rows)

INSERT 0 0
=#

Хотелось бы в последнем случае так же получить id вместо (0 rows). Это возможно без доп. селекта с помощью returning? Пробовал «RETURNING EXCLUDED.id» а также «RETURNING a.id».

returning возвращает только затронутые строки. Можешь сделать костылем

... do update set translated_name = (case when a.locked then translated_name else 'кот' end) returning id;

deadNightTiger ★★★★ ()
Последнее исправление: deadNightTiger (всего исправлений: 1)
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.