Search⌘ K
Home
Courses
The Art of PostgreSQL

Sequences and the Serial Pseudo Data Type

Explore how PostgreSQL manages sequences and serial pseudo data types such as smallserial, serial, and bigserial. Understand their behavior, how sequences operate outside transactions, and how integer limits affect serial columns including error scenarios and best practices.

We'll cover the following...

Other kinds of numeric data types in PostgreSQL are smallserial, serial, and bigserial. They’re actually pseudotypes—the parser recognizes their syntax but then transforms them into something else entirely:

PostgreSQL
CREATE TABLE tablename (
    colname SERIAL
);

This is equivalent to specifying:

PostgreSQL
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Sequence

We can use any of these two codes. The sequence SQL object is covered by the SQL standard and documented in the ...