PostgreSQL GENERATED ALWAYS And SERIAL

Faruk Erdem

2 years ago

In today’s article, I will explain the difference between PostgreSQL GENERATED ALWAYS and SERIAL.

The serial data type is one of the previously used data types, but it is not a SQL standard.

It complies with the generated as identity sql standards that come with Postgresql 10 version.

create table t1 (id serial primary key);
create table t2 (id integer primary key generated always as identity);

1 2	create table t1 (id serial primary key); create table t2 (id integer primary key generated always as identity);

While manually entering data in Serial data type, data cannot be entered without parameter given in generated always.

Let’s continue with the example:

Let’s create two separate columns with two separate data types with the same columns.

create table t1 (id serial primary key);

create table t2 (id integer primary key generated always as identity);

create table t1 (id serial primary key);

create table t2 (id integer primary key generated always as identity);

Let’s insert our tables in order.

Data has been added to our t1 table as above. Let’s try to insert the same value into our t2 table.

Our T2 table gave an error because when you add the genereated always parameter, it does not allow external data entry, but if you say that I absolutely must, you can insert it as follows.

insert into t2 (id) overriding system value values (1) ;

1	insert into t2 (id) overriding system value values (1) ;