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

Can you add your CREATE TABLE command for the n_clients table. — Frank Bollack
– Frank Bollack, Commented Sep 23, 2009 at 11:06
I updated my question, please have a look at my tables structure — Elitmiar
– Elitmiar, Commented Sep 23, 2009 at 11:08

Palantir · Accepted Answer · 2009-09-23 11:06:47Z

6

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.

answered Sep 23, 2009 at 11:06

Palantir

24.2k11 gold badges79 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pascal MARTIN · Accepted Answer · 2009-09-23 11:03:52Z

0

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.

answered Sep 23, 2009 at 11:03

Pascal MARTIN

402k82 gold badges665 silver badges666 bronze badges

Comments

Greg Smith · Accepted Answer · 2009-09-23 19:59:47Z

0

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.

answered Sep 23, 2009 at 19:59

Greg Smith

18.4k1 gold badge39 silver badges27 bronze badges

Comments

Goutham JM · Accepted Answer · 2021-03-31 05:39:51Z

0

The PostGresSQL should have a primary key while creating a DB , so you are not able to add anything include then only you can add data manually

answered Mar 31, 2021 at 5:39

Goutham JM

1

Comments

bobflux · Accepted Answer · 2021-01-18 15:58:47Z

-1

8.1 version is dated. 8.4 displays a much better error message :

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

edited Jan 18, 2021 at 15:58

answered Sep 26, 2009 at 9:35

bobflux

11.6k3 gold badges31 silver badges33 bronze badges

Collectives™ on Stack Overflow

Can't insert new row into postgres database table?

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related