Обсуждение: Strange primary key error on insertion
I have a strange issue (postgres 8.4) trying to insert old rows back into the s_tbls table. A check on the primary key (n_id) between s_tbl_import and s_tbls yields no matches, yet the index on s_tbls.n_id yields an error when attempting to insert: => select n_id from s_tbl_import where n_id IN ( select n_id from s_tbls); n_id ------ (0 rows) => insert into s_tbls (select * from s_tbl_import); ERROR: duplicate key value violates unique constraint "s_tbls_pkey" Table "s_tbls" Column | Type | Modifiers ------------------+-----------------------------+-------------------------------------------------------- n_id | integer | not null default nextval('s_tbls_n_id_seq'::regclass) dt_created | timestamp without time zone | default now() dt_modified | timestamp without time zone | default now() t_node | text | ... Indexes: "s_tbls_pkey" PRIMARY KEY, btree (n_id) Thanks for any assistance Rory
2011/10/6 Rory Campbell-Lange <rory@campbell-lange.net>
Looks like you had duplicates in s_tbl_import. Try this:
SELECT * FROM s_tbl_import WHERE n_id IN (
SELECT n_id from s_tbl_import group by n_id HAVING count(*)>1
);
I have a strange issue (postgres 8.4) trying to insert old rows back
into the s_tbls table. A check on the primary key (n_id) between
s_tbl_import and s_tbls yields no matches, yet the index on s_tbls.n_id
yields an error when attempting to insert:
=> select n_id from s_tbl_import where n_id IN (
select n_id from s_tbls);
n_id
------
(0 rows)
=> insert into s_tbls (select * from s_tbl_import);
ERROR: duplicate key value violates unique constraint "s_tbls_pkey"
Looks like you had duplicates in s_tbl_import. Try this:
SELECT * FROM s_tbl_import WHERE n_id IN (
SELECT n_id from s_tbl_import group by n_id HAVING count(*)>1
);
Table "s_tbls"
Column | Type | Modifiers
------------------+-----------------------------+--------------------------------------------------------
n_id | integer | not null default nextval('s_tbls_n_id_seq'::regclass)
dt_created | timestamp without time zone | default now()
dt_modified | timestamp without time zone | default now()
t_node | text |
...
Indexes:
"s_tbls_pkey" PRIMARY KEY, btree (n_id)
On 06/10/11, Filip Rembiałkowski (plk.zuber@gmail.com) wrote: > 2011/10/6 Rory Campbell-Lange <rory@campbell-lange.net> > > => insert into s_tbls (select * from s_tbl_import); > > > > ERROR: duplicate key value violates unique constraint "s_tbls_pkey" > > > > > Looks like you had duplicates in s_tbl_import. Try this: > SELECT * FROM s_tbl_import WHERE n_id IN ( > SELECT n_id from s_tbl_import group by n_id HAVING count(*)>1 > ); Hi Filip You were right! Thanks for catching my silly error. Rory