As of 2003 the SQL standard supports serial types for auto-generated values and identity-columns. A serial type is defined as a primary database object over the built-in integer data types.
serial_data_types:
SERIAL
| BIGSERIAL
| int_datatype AUTO_INCREMENT
| int_datatype GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ '(' seq_params ')' ]
int_datatype:
BIGINT
| INTEGER
| INT
| SMALLINT
| TINYINT
seq_params:
[ AS int_datatype ]
[ START WITH bigint ]
[ INCREMENT BY bigint ]
[ MINVALUE bigint | NO MINVALUE ]
[ MAXVALUE bigint | NO MAXVALUE ]
[ CACHE bigint ]
[ [ NO ] CYCLE ]
When a serial_data_type is used in a table column definition it will implicitly create a sequence and
set the column default clause to next value for "schemaname"."seq_####".
The default clause will be used during inserts when no value is specified for that column.
SERIAL will use an int as column data type, BIGSERIAL will use a bigint as column data type.
For AUTO_INCREMENT and GENERATED ... you need to specify which integer data type you want to use explicitly.
Note when using SERIAL or BIGSERIAL it will also implicitly generate
a PRIMARY KEY constraint and a NOT NULL constraint for that column.
It is also possible to CREATE and use SEQUENCEs yourself.
Associated system table: sys.sequences
Associated information_schema view: information_schema.sequences
CREATE TABLE test_serial (
id SERIAL, -- this will implicitly create a PKey. Use BIGSERIAL if you want the id to be of type bigint instead of int.
count INT AUTO_INCREMENT,
info INT GENERATED ALWAYS AS
IDENTITY (
START WITH 100 INCREMENT BY 2
NO MINVALUE MAXVALUE 1000
CACHE 2 CYCLE
)
);
INSERT INTO "test_serial";
SELECT * FROM "test_serial";
+------+-------+------+
| id | count | info |
+======+=======+======+
| 1 | 1 | 100 |
+------+-------+------+
INSERT INTO "test_serial";
SELECT * FROM "test_serial";
+------+-------+------+
| id | count | info |
+======+=======+======+
| 1 | 1 | 100 |
| 2 | 2 | 102 |
+------+-------+------+
INSERT INTO "test_serial";
SELECT * FROM "test_serial";
+------+-------+------+
| id | count | info |
+======+=======+======+
| 1 | 1 | 100 |
| 2 | 2 | 102 |
| 3 | 3 | 104 |
+------+-------+------+
-- show the columns info
select id, number, name, type, type_digits, table_id, "default", "null" from sys.columns
where table_id in (select id from sys.tables where name = 'test_serial');
+------+--------+-------+------+-------------+----------+---------------------------------+-------+
| id | number | name | type | type_digits | table_id | default | null |
+======+========+=======+======+=============+==========+=================================+=======+
| 7953 | 0 | id | int | 31 | 7961 | next value for "sys"."seq_7946" | false |
| 7956 | 1 | count | int | 31 | 7961 | next value for "sys"."seq_7947" | true |
| 7957 | 2 | info | int | 31 | 7961 | next value for "sys"."seq_7948" | true |
+------+--------+-------+------+-------------+----------+---------------------------------+-------+
-- show the implicitly created sequences info
select sq.*, get_value_for(s.name, sq.name) as current_value from sys.sequences sq join sys.schemas s on sq.schema_id = s.id;
+------+-----------+--------------+-------+----------------------+---------------------+-----------+----------+-------+---------------+
| id | schema_id | name | start | minvalue | maxvalue | increment | cacheinc | cycle | current_value |
+======+===========+==============+=======+======================+=====================+===========+==========+=======+===============+
| 7958 | 2000 | seq_7946 | 1 | 0 | 9223372036854775807 | 1 | 1 | false | 4 |
| 7959 | 2000 | seq_7947 | 1 | 0 | 9223372036854775807 | 1 | 1 | false | 4 |
| 7960 | 2000 | seq_7948 | 100 | -9223372036854775807 | 1000 | 2 | 2 | true | 106 |
+------+-----------+--------------+-------+----------------------+---------------------+-----------+----------+-------+---------------+
-- dump the definition of table "test_serial"
sql>\d test_serial
CREATE TABLE "sys"."test_serial" (
"id" INTEGER NOT NULL,
"count" INTEGER,
"info" INTEGER,
CONSTRAINT "test_serial_id_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "sys"."test_serial" ALTER COLUMN "id" SET DEFAULT next value for "sys"."seq_7946";
ALTER TABLE "sys"."test_serial" ALTER COLUMN "count" SET DEFAULT next value for "sys"."seq_7947";
ALTER TABLE "sys"."test_serial" ALTER COLUMN "info" SET DEFAULT next value for "sys"."seq_7948";
sql>\ds seq_7946
CREATE SEQUENCE "sys"."seq_7946" START WITH 4 NO CYCLE;
sql>\ds seq_7947
CREATE SEQUENCE "sys"."seq_7947" START WITH 4 NO CYCLE;
sql>\ds seq_7948
CREATE SEQUENCE "sys"."seq_7948" START WITH 106 INCREMENT BY 2 MINVALUE 1000 MAXVALUE 1000 CACHE 2 CYCLE;
-- when the table is dropped, the implicitly created sequences will also be removed.
DROP TABLE "test_serial";
SELECT * FROM sys.sequences;
+----+-----------+------+-------+----------+----------+-----------+----------+-------+
| id | schema_id | name | start | minvalue | maxvalue | increment | cacheinc | cycle |
+====+===========+======+=======+==========+==========+===========+==========+=======+
+----+-----------+------+-------+----------+----------+-----------+----------+-------+