PostgreSQL - SERIAL - Generate IDs (Identity, Auto-increment)

SERIAL data type allows you to automatically generate unique integer numbers (IDs, identity, auto-increment, sequence) for a column.

Quick Example:

   -- Define a table with SERIAL column (id starts at 1)
   CREATE TABLE teams
   (
      id SERIAL UNIQUE, 
      name VARCHAR(90)
   );
 
   -- Insert a row, ID will be automatically generated
   INSERT INTO teams (name) VALUES ('Tottenham Hotspur');
 
   -- Retrieve generated ID (just one of the possible options)
   SELECT LASTVAL(); 
   -- Returns: 1

PostgreSQL SERIAL data type:

Start Value		Always 1	ALTER SEQUENCE RESTART WITH to change
Increment		Always 1	ALTER SEQUENCE INCREMENT BY to change
How to Generate IDs	Omit the SERIAL column in INSERT, or specify DEFAULT keyword
Explicit ID Insert
Multiple SERIAL per Table
Constraints	NOT NULL	Added automatically
	Primary or unique key	Not required and not added automatically
Retrieve Last ID	LASTVAL()	Returns the last ID inserted in the current session
	CURRVAL('seq_name')	Returns the current ID for the specified sequence
	INSERT … RETURNING serialcol returns ID immediately after INSERT statement
Gaps	If a value is explicitly inserted, this has no effect on sequence generator
Restart (Reset)	ALTER SEQUENCE tablename_serialcol_seq RESTART WITH new_current_id;
Alternatives	BIGSERIAL	64-bit ID numbers
	Using a sequence and DEFAULT NEXTVAL('seq_name')
	OID system column
Synonym	SERIAL4

Version: PostgreSQL 9.1

When you define a SERIAL column, PostgreSQL automatically changes column to NOT NULL, creates a sequence tablename_serialcol_seq and DEFAULT NEXTVAL to select ID values from the sequence only if they are not supplied in INSERT statement:

   CREATE TABLE teams
   (
      id SERIAL, 
      name VARCHAR(90)
   );
 
   -- is equivalent to 
   CREATE SEQUENCE teams_id_seq;
   CREATE TABLE teams
   (
      id INT NOT NULL DEFAULT NEXTVAL('teams_id_seq'), 
      name VARCHAR(90)
   );

If you need a SERIAL column to be unique, you have to specify UNIQUE or PRIMARY KEY explicitly.

To generate a ID value, you can omit the SERIAL column in INSERT statement, or specify DEFAULT keyword:

   -- Omit serial column
   INSERT INTO teams (name) VALUES ('Aston Villa');
 
   -- Specify DEFAULT
   INSERT INTO teams VALUES (DEFAULT, 'Manchester City');

Note that you cannot insert NULL, but can insert 0. In MySQL these 2 values force ID generation, but this is not applied to PostgerSQL:

   INSERT INTO teams VALUES (NULL, 'Some team');
   -- ERROR: null value in column "id" violates not-null constraint
 
   INSERT INTO teams VALUES (0, 'Reserved');
   -- 1 row affected

Table content:

PostgreSQL SERIAL data type does not provide options to set the start value and increment, but you can modify the sequence object assigned to SERIAL using ALTER SEQUENCE statement:

   CREATE TABLE teams2
   (
      id SERIAL UNIQUE,
      name VARCHAR(90)
   );
 
   -- Modify initial value and increment
   ALTER SEQUENCE teams2_id_seq RESTART WITH 3 INCREMENT BY 3;
 
   -- Insert data
   INSERT INTO teams2 (name) VALUES ('Crystal Palace'); 
   INSERT INTO teams2 (name) VALUES ('Leeds United');

Table content:

There are several options to obtain the inserted ID value. You can use LASTVAL() function that returns the latest value for any sequence:

   INSERT INTO teams (name) VALUES ('Manchester United');
 
   SELECT LASTVAL();
   -- Returns: 4

You can also obtain the current value from the sequence object directly using CURRVAL function. The sequence name is tablename_serialcol_seq:

   INSERT INTO teams (name) VALUES ('Chelsea');
 
   SELECT CURRVAL('teams_id_seq');
   -- Returns: 5

Or you can use the RETURNING clause of INSERT statement to return ID:

   INSERT INTO teams (name) VALUES ('Arsenal') RETURNING id;
   -- Returns: 6

How to Access Generated ID in Application

SELECT LASTVAL() and SELECT CURRVAL return the generated ID as a single-row result set. In a .NET, Java or PHP application you can use appropriate methods to execute a query and read a row:

• PostgreSQL and C#.NET - Working with Result Sets

If you need to obtain ID in another PostgreSQL PL/pgSQL function, you can use INTO clause:

     DECLARE last_id  INT;
     ...
     INSERT INTO teams VALUES (...);
     SELECT LASTVAL() INTO last_id;
     -- or
     INSERT INTO teams VALUES (...);
     SELECT CURRVAL('teams_id_seq') INTO last_id;
     -- or in a single statement
     INSERT INTO teams VALUES (...) RETURNING id INTO last_id;

If you insert an ID value explicitly, it has no effect on the sequence generator, and its next value remains unchanged and will be used when you insert subsequent rows:

   -- Insert ID 8 explicitly
   INSERT INTO teams VALUES (8, 'Everton');
 
   -- Continue using ID generator
   INSERT INTO teams (name) VALUES ('Liverpool');  -- ID 7 is assigned

Note that the sequence generator may have conflicts with IDs that were already inserted using explicit values. You can get an error if there is an UNIQUE constraint, or duplicate IDs can be inserted:

   -- Will try to assign ID 8 that already inserted
   INSERT INTO teams (name) VALUES ('Some team');  
   -- ERROR:  duplicate key value violates unique constraint "teams_id_key"
   -- DETAIL:  Key (id)=(8) already exists.
 
   -- Continue, now it will use ID 9
   INSERT INTO teams (name) VALUES ('Newcastle United');

Table content:

If you remove rows from a table, you can insert removed IDs explicitly, it will not have any effect on the sequence generator.

You can change the current value of a sequence generator using ALTER SEQUENCE statement:

   ALTER SEQUENCE teams_id_seq RESTART WITH 31;
 
   -- ID 31 will be assigned
   INSERT INTO teams (name) VALUES ('Queens Park Rangers');

Serial (identity or auto-increment) columns in other databases:

Oracle:

Auto-increment or Identity

Can be emulated using a sequence and trigger

SQL Server:

IDENTITY(start, increment)

Increment can be specified

MySQL:

AUTO_INCREMENT Column Option
Start Value
Increment		Always 1
Generate ID	NULL or 0 force ID generation
Last ID	LAST_INSERT_ID() function
Restrictions	UNIQUE or PRIMARY KEY constraint is required

Converting PostgreSQL SERIAL columns:

Oracle:

Oracle does not support SERIAL (auto-increment, identity) columns, but this functionality can be implemented using a sequence and a trigger:

   CREATE TABLE teams
   ( 
     id NUMBER(10,0) UNIQUE, 
     name VARCHAR2(90)
   ); 
 
   CREATE SEQUENCE teams_id_seq START WITH 1 INCREMENT BY 1; 
 
   CREATE OR REPLACE TRIGGER teams_seq_tr 
    BEFORE INSERT ON teams FOR EACH ROW 
    WHEN (NEW.id IS NULL) 
   BEGIN 
    SELECT teams_id_seq.NEXTVAL INTO :NEW.id FROM dual; 
   END; 
   /

Note that a trigger is required as Oracle does not allow using NEXTVAL in DEFAULT clause for a column.

SQL Server:

SQL Server supports IDENTITY property and allows you to specify the initial and increment values:

   CREATE TABLE teams
   ( 
     id INT IDENTITY(1, 1) UNIQUE, 
     name VARCHAR(90)
   );

MySQL:

MySQL supports AUTO_INCREMENT column option that allows you to automatically generate IDs.

There is the table option AUTO_INCREMENT that allows you to define the start value, but you cannot define the increment, it is always 1:

   CREATE TABLE teams
   ( 
     id INT AUTO_INCREMENT UNIQUE, 
     name VARCHAR(90)
   )
   AUTO_INCREMENT = 1;   -- start value

Note that MySQL requires an unique or primary key constraint on AUTO_INCREMENT columns.

For more information, see Generating IDs in MySQL.

• PostgreSQL Articles and Reference
• PostgreSQL OID System Column - Table with OIDs
• PostgreSQL and C#.NET - Working with Result Sets
• Generating IDs in MySQL