Re: how do I capture conflicting rows

Поиск
Список
Период
Сортировка
От Rui DeSousa
Тема Re: how do I capture conflicting rows
Дата
Msg-id 6D26434B-74A9-43FF-B593-07DAD0BBFD9E@crazybean.net
обсуждение исходный текст
Ответ на how do I capture conflicting rows  (Nikhil Ingale <niks.bgm@gmail.com>)
Список pgsql-admin
Дерево обсуждения


On May 15, 2023, at 1:32 AM, Nikhil Ingale <niks.bgm@gmail.com> wrote:

INSERT INTO test (id,name,age,branch) SELECT * FROM student ON CONFLICT DO NOTHING;

How do I capture the conflicting records to a file while non conflicting records are inserted to the table?


You can return the rows inserted and from that you can determine which rows had conflicts by returning the inserted rows. 

with x (id, name, age, branch) as (
  select id, name, age, branch
  from student 
), insrt (id) as (
  insert into test (id,name,age,branch) 
  select id, name, age, branch from x 
  on conflict do nothing 
  returning id
)
select x.*
from x
left out join insrt on insrt.id = x.id
where insrt.id is null
;

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

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: Options for more aggressive space reclamation in vacuuming?
Следующее
От: Wolfgang Wilhelm
Дата:
Сообщение: Re: Options for more aggressive space reclamation in vacuuming?