Postgres ERROR: duplicate key value violates unique constraint

Question

Table definition

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 that a duplicate key value violates a unique constraint. So I found google and found the link

So i tried

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

It indicates a yntax error at or near the "WHERE" level.

How to use insert statement to use where clause so that when running the same request with php it does not return an error

column uid is unique

Answer 1

The INSERT statement does not support the WHERE clause. Run it.

 create table test ( n integer primary key ); insert into test values (1); insert into test values (2) where true; 

This will give you a syntax error due to the WHERE clause.

SELECT statements may have a WHERE clause. This will insert 2 into the test pattern once. Run it as many times as you want; this will not cause an error. (But it will only insert one maximum row.)

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

Thus, your request, assuming that you are trying to avoid increasing the error by 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) ;