Sequences
To use this feature you would need to have [email protected]
or higher and [email protected]
or higher
Sequences in PostgreSQL are special single-row tables created to generate unique identifiers, often used for auto-incrementing primary key values. They provide a thread-safe way to generate unique sequential values across multiple sessions.
Key Features
-
Creation and Initialization: Use CREATE SEQUENCE to create a new sequence. Parameters such as increment value, start value, min/max values, and cache size can be specified.
-
Manipulation Functions
nextval('sequence_name')
: Advances the sequence and returns the next value.currval('sequence_name')
: Returns the current value of the sequence for the current session.setval('sequence_name', value)
: Sets the sequence’s current value.lastval()
: Returns the last value returned by nextval in the current session.
-
Ownership: Sequences can be linked to table columns using the OWNED BY clause. Dropping the table or column will automatically drop the associated sequence.
-
Cycling: Sequences can be set to cycle when they reach their maximum or minimum value using the CYCLE option. The default is NO CYCLE.
-
Caching: Sequence values can be preallocated using the CACHE option for improved performance.
Limitations
- Gaps: Sequences are not gapless. Aborted transactions or crashes can lead to gaps in the sequence values.
- Concurrency: While sequences provide unique values across sessions, the values may be out of order when considering all sessions.
- No Rollback: Changes to sequences are not rolled back if a transaction fails. This ensures unique values but can lead to gaps.
- Crash Recovery: Unlogged sequences or sequences modified before a crash might not be properly restored to their previous state.
Practical Use
- Default Behavior: By default, sequences increment by 1 and start at 1.
- Custom Behavior: Custom start points, increments, min/max values, and cycling can be specified.
- Associations: Commonly associated with table columns for auto-incrementing fields, making management of unique identifiers seamless.
Usage Example
import { pgSchema, pgSequence } from "drizzle-orm/pg-core";
// No params specified
export const customSequence = pgSequence("name");
// Sequence with params
export const customSequence = pgSequence("name", {
startWith: 100,
maxValue: 10000,
minValue: 100,
cycle: true,
cache: 10,
increment: 2
});
// Sequence in custom schema
export const customSchema = pgSchema('custom_schema');
export const customSequence = customSchema.sequence("name");