How to create the equivalent of an SQL Server's identity column in Postgres

Question

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 when executing:

When saving a new Application,

SQL Server code:

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20), 
  towid integer not null IDENTITY
)

PostgreSQL code :

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20)
)

Why doesn't the towid field (which is a default field) not get generated automatically when executing through PostgreSQL ?

Any possible reason? Triggers? Procedures?

Answer 1

tl;dr

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

create table tower 
(
  npages integer, 
  ifnds integer, 
  ifnid integer, 
  name varchar(20), 
  towid integer GENERATED BY DEFAULT AS IDENTITY    -- 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).

CREATE TABLE person_ (

    id_ 
        INTEGER 
        GENERATED BY DEFAULT AS IDENTITY   -- 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.

ALTER TABLE person_ 
    ALTER COLUMN id_ 
    RESTART WITH 1000      -- 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 BY DEFAULT AS IDENTITY ( 
    START WITH 200 
    MINVALUE 100 
    MAXVALUE 205 
    CYCLE 
    INCREMENT BY 3 
) PRIMARY KEY

Adding 4 rows:

200

203

100

103