How To Create The Equivalent Of An Sql Server's Identity Column In Postgres

March 08, 2024 Post a Comment

There is an application which uses MSSQL as its back end. Now I am developing code so that it may use PostgreSQL. I have almost completed it except there is this one difference whe

Solution 1:

tl;dr

Now in Postgres 10, specify GENERATED BY DEFAULT AS IDENTITY per the SQL standard.

createtable tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20), 
  towid integer GENERATED BYDEFAULTASIDENTITY-- per SQL standard
)

Identity column

Postgres 10 now supports the concept of identity column, and uses standard SQL syntax. While I am no expert on MS SQL Server, I believe this new standard support is equivalent.

`GENERATED … AS IDENTITY`

The GENERATED … AS IDENTITY command used during CREATE TABLE creates an implicit sequence. The creation, naming, permissions, and dropping of that sequence is transparent to you, unlike with SERIAL. Very intuitive now. If you grant a use permission to the table, they get permission for the sequence. If you drop the table, the sequence is dropped automatically.

Two flavors of the standard syntax. The difference matters only if you pass a value rather than let a value be generated. Typically, people always rely on the generated value, so normally you would simply use the first version, GENERATED BY DEFAULT AS IDENTITY.

GENERATED BY DEFAULT AS IDENTITY
- Generates a value unless the INSERT command provides a value.
GENERATED ALWAYS AS IDENTITY
- Ignores any value provided by INSERT unless specifying OVERRIDING SYSTEM VALUE

See the CREATE TABLE page for documentation.

Read this interesting page by Peter Eisentraut. He explains some weird issues with SERIAL. No such issues with the new identity column feature. So there is no reason to use SERIAL anymore, no downsides, only upsides; SERIAL is supplanted by GENERATED … AS IDENTITY.

Note that an identity column is not necessarily a primary key, and is not automatically indexed. So you still need to specify PRIMARY KEY explicitly if that is your intention (as would be the case typically).

CREATETABLE person_ (

    id_ 
        INTEGER 
        GENERATED BYDEFAULTASIDENTITY-- Replaces SERIAL. Implicitly creates a SEQUENCE, specified as DEFAULT.PRIMARY KEY                        -- Creates index. Specifies UNIQUE. Marks column for relationships.
        ,

    name_ 
        VARCHAR( 80 )

) ;

The intention is for the internal implementation details to be hidden from you. No need for you to know the name of the sequence being generated under-the-covers. For example, you can reset the counter via the column without knowing the underlying sequence.

ALTERTABLE person_ 
    ALTERCOLUMN id_ 
    RESTART WITH1000-- Reset sequence implicitly, without a name.
;

Specifying identity implicitly:

Marks column NOT NULL
Creates a sequence
- Type of sequence matches the column ( 32-bit 64-bit etc. )
Ties the sequence to the column
- Inherits permissions
- Cascades dropping
- Remains tied to the column even if column renamed
Specifies the sequence as source of default values for that column

The identity column can take the same options as CREATE SEQUENCE:

START WITH start
MINVALUE minvalue | NO MINVALUE
MAXVALUE maxvalue | NO MAXVALUE
INCREMENT [ BY ] increment
CYCLE | NO CYCLE
CACHE cache
OWNED BY NONE( specifying ownership for identity column makes no sense to me as ownership is managed automatically )

Silly example of options:

id_ INTEGER 
GENERATED BYDEFAULTASIDENTITY ( 
    STARTWITH200 
    MINVALUE 100 
    MAXVALUE 205CYCLE 
    INCREMENT BY3 
) PRIMARY KEY

Adding 4 rows:

200
203
100
103

Solution 2:

PostgreSQL will create table exactly as you requested. It will not generate some column automatically (almost, but this is low level). You need add towid serial primary key to your request.

Lab Sql