From: | Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> |
---|---|
To: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Strange primary key error on insertion |
Date: | 2011-10-06 10:38:19 |
Message-ID: | CAP_rwwmDLMM0ha=Ed=A-JYEzAepVn=CvjL6asWEMz_YVmgeA4Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2011/10/6 Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
> 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)
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2011-10-06 11:00:20 | Re: Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ? |
Previous Message | Filip Rembiałkowski | 2011-10-06 10:34:32 | Re: Restoring 2 Tables From All Databases Backup |