Serial datatypes

Serial datatypes mk Thu, 04/01/2010 - 22:21

As of 2003 the SQL standard supports serial types (sequences). They are of particular use in auto-generating key values.A serial type is defined as a primary database object over any of the built-in data types. The NEXT VALUE operation generates the next value and can be used anywhere a value expression is allowed. Its name should be unique within the current schema. It can only be dropped when the tables mentioning it have previously been dropped.

generated_column:
    GENERATED ALWAYS AS IDENTITY [ '(' seq_params ')' ]
  | AUTO_INCREMENT

sequence_commands:
    CREATE SEQUENCE [schema_name '.'] seq_name [ seq_params ]
  | DROP SEQUENCE [schema_name '.'] seq_name
  | ALTER SEQUENCE [schema_name '.'] seq_name [alter_seq_params]

seq_params:
    [ AS datatype ]
    [ START WITH nonzero-bigint ]
    [ INCREMENT BY nonzerol-bigint ]
    [ MINVALUE nonzero-bigint | NO MINVALUE ]
    [ MAXVALUE nonzero-bigint | NO MAXVALUE ]
    [ CACHE nonzero-bigint ]
    [ [ NO ] CYCLE ]

alter_seq_params:
    [ AS datatype ]
    [ RESTART [WITH nonzero-bigint ] ]
    [ INCREMENT BY nonzerol-bigint ]
    [ MINVALUE nonzero-bigint | NO MINVALUE ]
    [ MAXVALUE nonzero-bigint | NO MAXVALUE ]
    [ CACHE nonzero-bigint ]
    [ [ NO ] CYCLE ]

Example. The example shown below introduces the column count, which is incremented with each row being added. It is conceptually identical to the value expression max(count)+1in each insert. The column info is a limited range with wrap around.The serial type as found in PostgreSQL and the auto_increment  flag as found in MySQL are both mapped onto a sequence type in MonetDB/SQL.

CREATE TABLE test_serial (
   d DATE,
   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
)      );

Much like other primary database objects, the sequence type can be altered at any time as illustrated below.

sql>CREATE SEQUENCE "my_test_seq" as integer START WITH 2;
sql>CREATE TABLE test (t int DEFAULT NEXT VALUE FOR "my_test_seq", v char);
sql>INSERT INTO test(v) VALUES ('a');
Rows affected 1
sql>INSERT INTO test VALUES (10, 'b');
Rows affected 1
sql>ALTER SEQUENCE "my_test_seq" RESTART WITH (SELECT MAX(t) + 1 FROM test);
sql>INSERT INTO test(v) VALUES ('c');
Rows affected 1
sql>SELECT * FROM test;
+----+---+
| t  | v |
+====+===+
| 2  | a |
| 10 | b |
| 11 | c |
+----+---+

The functions sys.get_value_for('<schema name>', '<sequence name>') and sys.next_value_for('<schema name>', '<sequence name>') can be used to query the current value of a sequence. The difference is that next_value_for() also advances the current value of a sequence to the next value.  The SQL statement SELECT NEXT VALUE FOR <schemal name>.<sequence name> is an equivalent of sys.next_value_for('<schema name>', '<sequence name>'). The following queries demonstrate how these functions and statement work:

sql>CREATE SEQUENCE "myseq" AS INTEGER;
operation successful
sql>SELECT get_value_for('sys', 'myseq');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>SELECT next_value_for('sys', 'myseq');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>SELECT NEXT VALUE FOR myseq;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple