Обсуждение: 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

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