How to insert value to identity column in PostgreSQL 11.1

Question

I would like to insert my own value to identity column.

Table Schema:

CREATE TABLE public.userdetail (
    userdetailid int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    username varchar(30) NOT NULL,
    "password" varchar(1000) NOT NULL,
    CONSTRAINT pk_userdetail PRIMARY KEY (userdetailid)
);

Insert Query:

INSERT INTO UserDetail (UserDetailId,UserName, Password) 
  VALUES(1,'admin', 'password');

Here insert query throwing below error:

cannot insert into column "userdetailid"

Is there any command exists to force insert to identity column like MS SQL :

 SET IDENTITY_INSERT UserDetail ON

Let me know if you have any solution.

Answer 1

GENERATED ALWAYS tell Postgres to always generate value for identity column. Postgres will throw error if you try to insert value in such a column.

If you want to insert, your can use following query

INSERT INTO UserDetail (UserName, Password) 
  VALUES('admin', 'password');

If you really want to insert into identity column, you can use GENERATED BY DEFAULT instead of GENERATED ALWAYS. In that case if you haven't provided value for identity column Postgres will use generated value.

Or

you can use OVERRIDING SYSTEM VALUE as shown below

INSERT INTO UserDetail (UserDetailId,UserName, Password) 
OVERRIDING SYSTEM VALUE 
  VALUES(1,'admin', 'password');