Creating Sequences in PostgreSQL
What are Sequences in PostgreSQL?
Sequences in PostgreSQL are special database objects designed to generate unique numeric identifiers automatically. They're commonly used for primary key columns to ensure each row has a unique identifier.
Adding a Sequence to an Existing Table
Sometimes you may need to add a sequence to a table that was created without one. Here's how to do it:
-- Step 1: Create the sequence
CREATE SEQUENCE IF NOT EXISTS objects_id_object_seq;
-- Step 2: Set the sequence value to the current maximum ID
SELECT setval('objects_id_object_seq', (SELECT max(id_object) FROM objects));
-- Step 3: Alter the table to use the sequence as default
ALTER TABLE objects ALTER COLUMN id_object SET DEFAULT nextval('objects_id_object_seq');Understanding Each Step
- Creating the sequence: The
IF NOT EXISTSclause prevents errors if the sequence already exists (available in PostgreSQL 9.5+). - Setting the initial value: The
setvalfunction sets the sequence's current value to match the highest existing ID in the table. - Linking to the table: The final step configures the column to automatically use the sequence for new rows.
Common Sequence Operations
| Operation | Command | Description |
|---|---|---|
| Create sequence | CREATE SEQUENCE name |
Creates a new sequence |
| Get next value | nextval('sequence_name') |
Returns the next value from the sequence |
| Get current value | currval('sequence_name') |
Returns the most recently obtained value |
| Set sequence value | setval('sequence_name', value) |
Sets the current value of the sequence |
| Drop sequence | DROP SEQUENCE sequence_name |
Removes the sequence |
Best Practices
- Name sequences consistently (table_column_seq is a common pattern)
- Always check existing maximum values when adding sequences to tables with data
- Consider using SERIAL or BIGSERIAL data types for new tables (they create sequences automatically)
- In PostgreSQL 10+, consider using identity columns instead of sequences
Learn More
For more detailed information, visit the PostgreSQL documentation on CREATE SEQUENCE.