How to use SERIAL in PostgreSQL

Official documentation: Serial Types

What is SERIAL?#

SERIAL is a notational convenience for creating unique identifier columns in PostgreSQL. It creates an auto-incrementing integer column, commonly used for primary keys. When you declare a column as SERIAL, PostgreSQL automatically:

1. Creates a sequence object
2. Sets the column default to draw from that sequence
3. Applies a NOT NULL constraint to the column

SERIAL Types#

PostgreSQL offers three SERIAL types:

Creating Tables with SERIAL#

Basic Usage#

-- Create a table with a SERIAL primary key
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2)
);

-- Insert without specifying ID (auto-generated)
INSERT INTO products (name, price) VALUES ('Widget', 19.99);

What Happens Behind the Scenes#

When you create a SERIAL column, PostgreSQL actually executes commands similar to:

-- What happens when you use SERIAL
CREATE SEQUENCE products_id_seq;
CREATE TABLE products (
    id INTEGER NOT NULL DEFAULT nextval('products_id_seq'),
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2)
);
ALTER SEQUENCE products_id_seq OWNED BY products.id;

Working with SERIAL Columns#

Inserting Data#

-- Let PostgreSQL generate the ID
INSERT INTO products (name, price)
VALUES ('Widget', 19.99)
RETURNING id;

-- Explicitly set ID (use with caution)
INSERT INTO products (id, name, price)
VALUES (100, 'Special Widget', 29.99);

Viewing Sequence Information#

-- Get information about the sequence
SELECT * FROM pg_sequences
WHERE sequencename = 'products_id_seq';

-- Get current sequence value
SELECT currval('products_id_seq');

-- Get next sequence value without advancing
SELECT nextval('products_id_seq');

Resetting or Altering Sequences#

-- Reset sequence to start from a specific value
ALTER SEQUENCE products_id_seq RESTART WITH 1000;

-- Change increment value
ALTER SEQUENCE products_id_seq INCREMENT BY 10;

Best Practices#

1. Choose the Right Type: Select the appropriate SERIAL type based on the expected number of rows:

   • SMALLSERIAL for tables with fewer than 32K rows
   • SERIAL for most tables (up to 2 billion rows)
   • BIGSERIAL for tables that may exceed 2 billion rows

2. Consider Using IDENTITY: For new PostgreSQL applications (version 10+), consider using the standard SQL IDENTITY syntax instead:

   CREATE TABLE modern_products (
       id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       name VARCHAR(100) NOT NULL
   );

3. Handle Sequence Gaps: Understand that SERIAL values may have gaps due to:

   • Rolled back transactions
   • Failed inserts
   • Explicit ID assignments
   • Sequence adjustments

4. Bulk Inserts Optimization: For bulk inserts, consider pre-allocating sequence values:

   SELECT nextval('products_id_seq')
   FROM generate_series(1, 1000);

5. Use with Foreign Keys: When using SERIAL columns as both primary key and foreign key targets, ensure consistency in type selection.

Common Issues and Solutions#

Sequence Not Advancing After Import#

After importing data, the sequence might not be updated to reflect the highest ID:

-- Find the maximum ID value
SELECT MAX(id) FROM products;

-- Update the sequence to continue after the max value
SELECT setval('products_id_seq', (SELECT MAX(id) FROM products));

Duplicate Key Violations#

When restoring data or performing migrations:

-- Check current sequence value
SELECT last_value FROM products_id_seq;

-- If needed, update sequence to avoid conflicts
SELECT setval('products_id_seq', (SELECT MAX(id) FROM products) + 1);

Performance Considerations#

For high-throughput insert operations, sequence generation can become a bottleneck:

-- Increase sequence cache for better performance
ALTER SEQUENCE products_id_seq CACHE 100;

Migrating from SERIAL to IDENTITY#

If you want to migrate from SERIAL to the newer IDENTITY syntax:

-- Step 1: Create a new table with IDENTITY
CREATE TABLE products_new (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2)
);

-- Step 2: Copy data
INSERT INTO products_new (id, name, price)
SELECT id, name, price FROM products;

-- Step 3: Set correct sequence value
SELECT setval(pg_get_serial_sequence('products_new', 'id'),
              (SELECT MAX(id) FROM products_new));

-- Step 4: Rename tables to complete migration
ALTER TABLE products RENAME TO products_old;
ALTER TABLE products_new RENAME TO products;

SERIAL vs IDENTITY: Making the Right Choice#

SERIAL (Traditional Approach)#

• Used in PostgreSQL for many years
• Widely documented in legacy code examples
• Creates an explicit sequence object
• Requires manual handling for some edge cases

IDENTITY (Modern Approach, PostgreSQL 10+)#

• SQL standard compliant
• Cleaner syntax and more predictable behavior
• Better manages the sequence internally
• Offers more control with ALWAYS/BY DEFAULT options
• Handles dumps and restores more elegantly

References#

• PostgreSQL Documentation: Serial Types
• PostgreSQL Documentation: Identity Columns
• PostgreSQL Documentation: Sequences