liveBook · Manning

Bigserial

As explained in Just Use Postgres!

BIGSERIAL is a PostgreSQL pseudo-type that creates a 64-bit integer column backed by a sequence for automatically generated values.

BIGSERIAL is a PostgreSQL pseudo-type for automatically generated 64-bit integer identifiers. PostgreSQL backs the column with a sequence, making it useful when an application may outgrow the 32-bit range provided by SERIAL. section 2.1.3 section 11.2

Use BIGSERIAL when:

  • A table needs automatically assigned numeric primary keys. section 2.1.3
  • The expected identifier range may exceed SERIAL’s 32-bit capacity; BIGSERIAL supports 64-bit integers up to 9,223,372,036,854,775,807. section 2.1.3
  • A queue needs monotonically generated message IDs for identifying and ordering records. section 11.2

Create a reviews table

CREATE TABLE products.reviews ( id BIGSERIAL PRIMARY KEY, product_id INT, customer_id INT, review TEXT, rank SMALLINT ); PostgreSQL creates the table’s identifier column and an associated sequence, visible with the psql \d command. Inserts can omit id; the sequence supplies it automatically.

Each concurrent producer may wait for the sequence to generate the next ID. If sequence generation becomes a bottleneck, UUIDs provide an alternative identifier strategy. section 11.2

How Just Use Postgres! covers it

How deep it goes
Explains declaration, sequence backing, numeric capacity, and contention, but does not detail sequence configuration.
Examples
Uses product reviews and a PostgreSQL message queue with BIGSERIAL primary keys.
What it assumes
Assumes basic PostgreSQL table creation and contrasts BIGSERIAL with SERIAL without requiring prior sequence configuration.
What you can do after
Build it from scratch by declaring BIGSERIAL columns for automatically generated table or message identifiers.
How it connects
Connects BIGSERIAL with sequences, SERIAL, primary keys, message queues, and UUID identifiers.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.