PostgreSQL Data Types - SERIAL

Versions: PostgreSQL 8.x and 7.x

PostgreSQL - SERIAL
Syntax	SERIAL
Data	32-bit auto-increment integer data, NOT NULL constraint is applied, UNIQUE and PRIMARY KEY constraints are not automatically assigned
Range	-2³¹ to 2³¹-1
Storage Size	4 bytes
Internals	Implemented as INTEGER with DEFAULT nextval for a sequence
Synonyms	SERIAL4
Version Differences	PostgreSQL 7.3 - SERIAL is automatically defined as UNIQUE
Standards	PostgreSQL Extension

The following definition

CREATE TABLE tab (
  col SERIAL
);

is equivalent to specifying

CREATE SEQUENCE tab_col_seq;
CREATE TABLE tab (
  col INTEGER NOT NULL DEFAULT nextval('tab_col_seq')
);
ALTER SEQUENCE tab_col_seq OWNED BY tab.col;

PostgreSQL SERIAL - Equivalents in Other Databases

Database	Data Type and Conversion
Oracle	NUMBER(10,0) with SEQUENCE object
SQL Server	INT with IDENTITY property
MySQL	INT with AUTO_INCREMENT property
Sybase ASE	NUMERIC(10,0) with IDENTITY property
Informix	SERIAL

Related Data Types in PostgreSQL

All Data Types

Oracle

SQL Server

IBM DB2

MySQL

PostgreSQL

Sybase ASE

Sybase ASA

Informix DS

Teradata

HP Neoview

Interbase/Firebird

Discussion