Postgres ERROR:duplicate key value violates unique constraint

Question

Table defintion is

create table users (
serial_no integer PRIMARY KEY DEFAULT nextval('serial'),
uid bigint NOT NULL,
username varchar(32),
name text,
CONSTRAINT production UNIQUE(uid)
);

I used this query

INSERT INTO users (uid) values(123) ;

It says duplicate key value violates unique constraint. So I googled it and found this link

So I tried

INSERT INTO users (uid) values(123) 
where 1 in (select 1 from users where uid = 123) ;

It says yntax error at or near "WHERE".

How to use a statement of insert into using the where clause so that when I run the same query using php it does not return an error

column uid is unique

What is the table definition? The error message seems pretty darn clear. It looks like there is a unique constraint on the table that is preventing you from inserting more than 1 row with a `uid = 123`. — Matt Ball, Feb 11 '12 at 19:11
There's already a row with a uid of 123. The database is behaving correctly. What's your question? — zgpmax, Feb 11 '12 at 19:12
If you execute this query, `select count(*) from users where uid = 123;`, what do you get? — Mike Sherrill 'Cat Recall', Feb 11 '12 at 19:33
@AnubhavAgarwal: So you already have one row with a unique uid of 123. Why are you trying to insert two such rows, and what do you want to happen when you try to violate a unique constraint? — Mike Sherrill 'Cat Recall', Feb 12 '12 at 02:29

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-02-12T02:46:08.330

The INSERT statement doesn't support a WHERE clause. Run this.

create table test (
  n integer primary key
);

insert into test values (1);
insert into test values (2) where true;

That will give you a syntax error because of the WHERE clause.

SELECT statements can have a WHERE clause, though. This will insert 2 into the test table one time. Run it as many times as you want; it won't raise an error. (But it will insert only one row at the most.)

insert into test (n) 
select 2 where 2 not in (select n from test where n = 2);

So your query, assuming you're trying to avoid raising an error on a duplicate key, should be something like this.

INSERT INTO users (uid) 
SELECT 123 WHERE 123 not in (SELECT uid FROM users WHERE uid = 123) ;

Postgres ERROR:duplicate key value violates unique constraint

1 Answers1

Linked