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.

Don't specify `UserDetailId` , either in the `values()` or in the `into..`. It will be automatically inserted, that's what identity column means. — Kaushik Nayak, Apr 16 '19 at 05:24

Fathah Rehman P · Accepted Answer · 2019-04-16T05:36:46.163

44

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

edited Apr 16 '19 at 05:36

answered Apr 16 '19 at 05:27

Fathah Rehman P

8,401
4
40
42

Is there any command exists to force insert to identity column like MS SQL :- SET IDENTITY_INSERT UserDetail ON; – Nayan Rudani Apr 16 '19 at 05:32
@NayanRudani - Please check modified answer – Fathah Rehman P Apr 16 '19 at 05:37
@FathahRehmanP What if `UserDetailId = 1` is already present in table. – Rajnish kumar Apr 23 '21 at 10:51
@Rajnishkumar If `UserDetailId` is a `PRIMARY KEY`, then the database will reject any attempt to duplicate a value. You would normally only write to an identity column if you’re trying to restore missing data. – Manngo Jun 22 '21 at 22:51
3

Note that inserting an explicit value into an automatically incremented field doesn't update the sequence that Postgres uses to generate new values for that field, which has the potential to cause conflicts later on if you don't manually update the sequence after inserting a value explicitly. See [this other question](https://stackoverflow.com/questions/9108833/postgres-autoincrement-not-updated-on-explicit-id-inserts) for information on updating autoinc sequences. – Nick Muise Aug 17 '21 at 16:08

How to insert value to identity column in PostgreSQL 11.1

1 Answers1