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
towid serial not null
in Postgres: postgresql.org/docs/current/static/…