Create and Manage Sequences

Ever inserted a row and stared at the id column, wondering how it magically became a unique number? Or worse — have you ever hit a duplicate key error because you hand-rolled your own counters instead of letting the database do its job? If so, you've felt the pain this lesson cures. PostgreSQL sequences are the invisible workhorses behind auto-incrementing primary keys, invoice numbers, and any unique numeric identifier your application needs. In this lesson, you'll learn to create and manage sequences like a pro — from basic CREATE SEQUENCE syntax to tuning cache sizes and handling edge cases — so your data stays consistent and your queries stay fast.

The problem this lesson solves

Picture this: you're building an e-commerce platform. Each order needs a unique ID. You could write application code to generate numbers, but what happens when two requests hit the server at the exact same millisecond? Duplicate IDs, corrupted orders, and a very angry customer. Or maybe you're importing legacy data and need to ensure new records don't collide with existing IDs. Without a single source of truth, chaos ensues.

Sequences solve this by providing a thread-safe, transaction-safe way to generate unique numbers directly inside the database. PostgreSQL guarantees that each call to nextval() returns a distinct value, no matter how many concurrent connections are hammering away. This isn't just about convenience — it's about data integrity, scalability, and avoiding the classic "race condition" that plagues naive counter implementations.

By the end of this lesson, you'll be able to create and manage sequences with confidence, understanding not just the syntax but also the mental model behind it. You'll move from "I use SERIAL because it works" to "I choose SEQUENCE because I understand its behavior."

Core concept / mental model

Think of a sequence as a number dispenser. Like those ticket machines at a deli — you press a button, and a machine spits out the next ticket number. The machine doesn't care who takes the ticket; it just ensures no two people get the same number. PostgreSQL's sequence works the same way: you call nextval('sequence_name'), and it returns the next number in line, automatically incrementing its internal counter.

But unlike a deli ticket, PostgreSQL sequences are non-transactional. That means once a number is retrieved, it's gone forever — even if the transaction rolls back. This is actually a feature: it prevents two concurrent transactions from ever getting the same number, at the cost of potential gaps. If you need gapless numbers (e.g., for legal invoices), sequences alone won't cut it, but for most use cases the trade-off is worth it.

Here's the anatomy of a sequence:

• Definition: A named database object that stores a counter.
• Key functions: nextval(regclass) to get the next value, currval(regclass) to see the last value in the current session, and setval(regclass, bigint) to jump to a specific value.
• Properties: start value, increment step, minimum/maximum bounds, cache size, and whether it cycles.

Sequences are often attached to a column via a DEFAULT expression, like nextval('my_sequence'), which is exactly what the SERIAL pseudo-type does under the hood. But SERIAL is just sugar — the real control comes from managing the sequence directly.

How it works step by step

Let's trace the lifecycle of a sequence:

1. Creation: You issue CREATE SEQUENCE with optional parameters. PostgreSQL allocates the sequence object and sets its current value to start minus increment (so the first nextval() returns start).

2. Usage: When you call nextval(), PostgreSQL fetches the next value, increments the counter, and returns the value. If you've set a CACHE greater than 1, PostgreSQL grabs a batch of numbers and hands them out from cache until exhausted — this improves concurrency.

3. Inspection: You can query pg_sequences system view to see current state, or use currval() within the same session to see the last value you received.

4. Alteration: If you need to change the increment, min, max, or restart, you use ALTER SEQUENCE.

5. Reset: To force the sequence to a specific value (e.g., after a bulk import), use setval().

6. Cleanup: When the sequence is no longer needed, DROP SEQUENCE removes it. But be careful: if a column's DEFAULT references it, the drop fails unless you use CASCADE.

Let's see each step in action.

Hands-on walkthrough

We'll create a sequence, use it in a table, and manage it. Open psql and follow along.

Step 1: Create a basic sequence

CREATE SEQUENCE order_number_seq
    START WITH 1000
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Expected output: CREATE SEQUENCE

We just created a sequence that starts at 1000 and increments by 1. The CACHE 1 means no pre-allocation — fine for learning.

Step 2: Retrieve the next value

SELECT nextval('order_number_seq');

Expected output:

 nextval
---------
    1000
(1 row)

Call it again and you'll get 1001, then 1002.

Step 3: Check the current value in the session

SELECT currval('order_number_seq');

Expected output: 1002 (or whatever the last nextval returned).

Step 4: Set the sequence to a specific value

Maybe you imported old orders and want the sequence to start at 5000. Use setval:

SELECT setval('order_number_seq', 5000);

Expected output: 5000

Now the next nextval() returns 5001.

Step 5: Use the sequence as a default

Create a table and tie the sequence to a column:

CREATE TABLE orders (
    id bigint PRIMARY KEY DEFAULT nextval('order_number_seq'),
    customer_name text NOT NULL
);

Insert without specifying id:

INSERT INTO orders (customer_name) VALUES ('Alice'), ('Bob');

Now query:

SELECT * FROM orders;

Expected output:

 id  | customer_name
-----+---------------
 5001 | Alice
 5002 | Bob
(2 rows)

Note: We used nextval directly. If you use SERIAL, PostgreSQL creates the sequence and default automatically, but you lose direct control. Our way gives you the handle to manage it later.

Step 6: Alter the sequence

Change the increment step to 10:

ALTER SEQUENCE order_number_seq INCREMENT BY 10;

Now nextval() returns 5012, then 5022, and so on.

Step 7: Reset the sequence to a new start

ALTER SEQUENCE order_number_seq RESTART WITH 10000;

Now the next value is 10000.

Step 8: Drop the sequence

If you no longer need it, drop it. But the orders table still references it, so you need CASCADE (which will also drop the default from the table):

DROP SEQUENCE order_number_seq CASCADE;

Expected output: DROP SEQUENCE

But wait — after cascading, the orders table's id column will have no default, so future inserts without id will fail. Better to remove the default first:

ALTER TABLE orders ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE order_number_seq;

Now the table is clean.

Compare options / when to choose what

You have several ways to generate unique IDs in PostgreSQL. Here's a comparison:

When to choose what: For most web apps with a single database, explicit CREATE SEQUENCE gives you the best balance of control and simplicity. If you're on PostgreSQL 10+, consider GENERATED ALWAYS AS IDENTITY for std compliance — it's like SERIAL but more standardized. If you need to be able to manage sequence state programmatically (e.g., jumping values after data migration), go with explicit sequences.

Pro tip: If you ever need to reset an identity column, use ALTER TABLE ... ALTER COLUMN ... RESTART WITH — it's cleaner than messing with the sequence directly.

Troubleshooting & edge cases

Sequence value out of range

If your sequence hits its max (default is bigint max ~9.2e18), you'll get an error. To fix, use ALTER SEQUENCE ... MAXVALUE 0 (or a bigger value) or NO MAXVALUE.

Gaps are normal

Your IDs might jump from 5 to 10 because a transaction rolled back after nextval(). This is by design. Don't panic. If you need gapless sequences, you'll need a different approach (e.g., locking a table and counting max(id)+1) — but that hurts concurrency.

Duplicate key errors after data import

Importing rows with explicit IDs can leave the sequence behind. Example: You import 100 rows with IDs 1–100, but the sequence's last value is 0, so the next insert tries to use 1 again and fails. Fix it with:

SELECT setval('your_sequence', (SELECT MAX(id) FROM your_table));

currval() error: not yet defined in this session

If you call currval() before any nextval() in the same session, you get an error. Always ensure a nextval() has been called, or use last_value from pg_sequences (but that's also session-independent).

Sequence permissions

By default, only the owner has USAGE on the sequence. If another role needs to use it, grant:

GRANT USAGE ON SEQUENCE order_number_seq TO app_user;

Dropping a sequence that's in use

If a table default references the sequence, DROP SEQUENCE fails with a dependency error. Use CASCADE only if you understand the cascade will drop the default column — or better, manually remove the default first.

What you learned & what's next

You've mastered creating and managing sequences in PostgreSQL. You can now: - Explain the core idea behind sequences: a non-transactional counter that generates unique numbers safely. - Create and manage sequences via CREATE SEQUENCE, ALTER SEQUENCE, and setval(). - Apply sequences as defaults for columns, and choose between SERIAL, IDENTITY, and explicit sequences. - Troubleshoot common issues like gaps, out-of-range values, and duplicate key errors after imports.

Sequences are just one piece of the puzzle. Next, you'll dive into autoincrementing columns and IDENTITY columns, where you'll learn about the GENERATED AS IDENTITY feature and how it compares to SERIAL. That's the natural next step — you'll see how to use sequences without even naming them.

Pro tip: Before moving on, play with CREATE SEQUENCE with different CACHE sizes and observe how nextval() behaves. Understanding caching will help you tune performance later.