Re: INSERT .. SELECT should redo SELECT if a duplicate key is found
От | Cesar Eduardo Barros |
---|---|
Тема | Re: INSERT .. SELECT should redo SELECT if a duplicate key is found |
Дата | |
Msg-id | 20020706170426.GC26802@cerberus.elnet.grupomk обсуждение исходный текст |
Ответ на | INSERT .. SELECT should redo SELECT if a duplicate key is found (Cesar Eduardo Barros <cesarb@elnetcorp.com.br>) |
Список | pgsql-bugs |
On Sat, Jul 06, 2002 at 12:18:56PM -0300, wrote: > > When you are trying to insert a row in a table with a UNIQUE constraint, > unless it already exists, you can try something like: > > INSERT INTO table (id) SELECT val WHERE NOT EXISTS (SELECT 1 FROM table WHERE id = val) > > However, this does not work as expected if another backend inserts a row > with the same unique column(s). > The same thing also happens with DELETE: psql 1: teste=# create table teste (id integer primary key, parent integer references teste (id)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'teste_pkey' for table 'teste' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE teste=# insert into teste values (1, null); INSERT 826882 1 teste=# begin; BEGIN psql 2: teste=# begin; BEGIN teste=# select 1 from teste where id = 1 for update; ?column? ---------- 1 (1 row) psql 1: teste=# delete from teste where not exists (select 1 from teste where parent = 1); [sits there waiting] psql 2: teste=# insert into teste values (2,1); INSERT 826884 1 teste=# commit; COMMIT psql 1: ERROR: <unnamed> referential integrity violation - key in teste still referenced from teste teste=# commit; COMMIT So, the problem is probably with the subselects. I wonder if UPDATE has the same problem. -- Cesar Eduardo Barros ElNet Hightech -- Administrador de Sistemas Unix cesarb@elnetcorp.com.br
В списке pgsql-bugs по дате отправления: