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

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:

• Integer Type
• Arbitrary Precision Numbers
• Floating-Point Numbers