CODEWITHSUNDEEP
databasepostgresql
Nayan Rudani
Nayan Rudani

Reputation: 1189

How to insert value to identity column in PostgreSQL 11.1

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.

Upvotes: 23

Views: 48117

Answers (2)

Roman Kulikov
Roman Kulikov

Reputation: 33

For the future generations... Here is 2 examples of same behavior:

The first one is like we did in the old days - serial column. With this approach we're able to insert default value into serial column and it will works - sequence will make its work and generate value.

drop table if exists public.test2;
create table public.test2 (
  a serial4 primary key,
  b int2
);

insert into public.test2 (b) values (1);
select * from public.test2;

insert into
  public.test2 (a,b)
values
  (1, 1),
  (default, 2)
on conflict
  (a)
do update set
  b = excluded.b;

select * from public.test2;
select currval(pg_get_serial_sequence('public.test2', 'a'));

In order to achieve the same behavior with identity column you should specify additional overriding system value.

drop table if exists public.test;
create table public.test (
  a int4 generated always as identity primary key,
  b int2
);

insert into public.test (b) values (1);
select * from public.test;

insert into
  public.test (a,b)
overriding system value
values
  (1, 1),
  (default, 2)
on conflict
  (a)
do update set
  b = excluded.b;

select * from public.test;
select currval(pg_get_serial_sequence('public.test', 'a'));

Upvotes: 1

Fathah Rehman P
Fathah Rehman P

Reputation: 8741

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

Upvotes: 59

Related Questions