Serial Numbers In PostgreSQL Explained

Nov. 20, 2018

This post is part of a series on the numeric data types found in PostgreSQL. Read on to learn all about defining arbitrary precision numbers in PostgreSQL. Write a comment if you have any questions or corrections!

Serial Numbers

Name Storage size Range
smallserial 2 bytes 1 - 32,767
serial 4 bytes 1 - 2,147,483,647
bigserial 8 bytes 1 - 9,223,372,036,854,775,807

The serial data type is PostgreSQL's way of specifying an autoincrementing numeric column. In this sense, it is not a true type. It's pretty convenient though. Specifying:

CREATE TABLE books (
  id SERIAL
);

is equivalent to this:

CREATE SEQUENCE books_id_seq;
CREATE TABLE books (
    id INTEGER NOT NULL DEFAULT nextval('books_id_seq')
);
ALTER SEQUENCE books_id_seq OWNED BY books.id;

Definitely the first option is much less verbose! Typically you see the serial type used with the UNIQUE or PRIMARY KEY constraints. Also, you'll notice that a serial data type has the NOT NULL constraint applied to it automatically, to ensure that a null value can't be inserted.

The serial types are equivalent to the integer types as far as their positive range goes.

  • smallserial (range: 1 - 32,767) = positive smallint range (1 - 32,767)
  • serial = int
  • bigserial = bigint

More Numeric Types

Learn about the other numeric data types in one of the following posts: