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
– Matt Ball, Commented Feb 11, 2012 at 19:11
There's already a row with a uid of 123. The database is behaving correctly. What's your question? — zgpmax
– zgpmax, Commented Feb 11, 2012 at 19:12
If you execute this query, select count(*) from users where uid = 123;, what do you get? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Feb 11, 2012 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'
– Mike Sherrill 'Cat Recall', Commented Feb 12, 2012 at 2:29

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-02-12 02:46:08Z

5

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) ;

edited Feb 12, 2012 at 2:46

answered Feb 12, 2012 at 2:40

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

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

Collectives™ on Stack Overflow

Postgres ERROR:duplicate key value violates unique constraint

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related