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

Answer 1

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