=# 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».
