Can't insert new row into postgres database table?

Question

I have an issue, I'm trying to insert a new row into a postgres database table and get the following error

ERROR:  duplicate key violates unique constraint "n_clients_pkey"

Here my query

insert into n_clients(client_name) values( 'value');

I'm using postgres 8.1.11

 PostgreSQL 8.1.11 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)

Here's the structure for my table

                                                Table "public.n_clients"
   Column    |           Type           |                               Modifiers                               
-------------+--------------------------+-----------------------------------------------------------------------
 id          | integer                  | not null default nextval(('public.n_clients_id_seq'::text)::regclass)
 client_name | character varying(200)   | not null
 moddate     | timestamp with time zone | default now()
 createdate  | timestamp with time zone | default now()
Indexes:
    "n_clients_pkey" PRIMARY KEY, btree (id)

and the sequence

Sequence "public.n_clients_id_seq"
Column     |  Type

---------------+--------- sequence_name | name last_value | bigint increment_by | bigint max_value | bigint min_value | bigint cache_value | bigint log_cnt | bigint is_cycled | boolean is_called | boolean

Answer 1

You must have a UNIQUE constraint on your table, that your insert is violating -- ie, considering the name of your table and index, you are probably trying to insert a client that already exists in your table.

Answer 2

This row exists already, therefore you cannot insert it. What is the primary key of your relation? Is it a sequence? If so, maybe it got stuck (maybe you imported data). You should reset it manually to the next free ID available (e.g., if the maximum ID is 41, you should do: SELECT setval('your_seq', 42);) then try again.

Answer 3

Typically one gets into this situation by manually adding a record with an id field that matches the current value for the sequence. It's easy to introduce this by some common dump/reload operations for example. I wrote an article about correcting for this sort of error across the entire database at Fixing Sequences.

Answer 4

WTF are you doing with 8.1 version from a century ago ? 8.4 displays a much better error message :

ERROR: duplicate key value violates unique constraint "master_pkey" DETAIL: Key (id)=(1) already exists.