WikiGalaxy
PostgreSQL Sequence
Introduction to Sequences
In PostgreSQL, a sequence is a database object that is used to generate a sequence of unique numbers, often used for auto-incrementing primary keys.
CREATE SEQUENCE user_id_seq;
Creating a Sequence
The above command creates a new sequence named user_id_seq. This sequence can be used to generate unique identifiers for user records.
Using Sequences in Tables
Associating Sequences with Columns
Sequences are often used in conjunction with table columns to auto-increment values. The nextval function is used to get the next value from a sequence.
CREATE TABLE users (
id INT PRIMARY KEY DEFAULT nextval('user_id_seq'),
name VARCHAR(100)
);
Table Definition with Sequences
In this example, the id column uses the user_id_seq sequence to auto-increment its values whenever a new record is inserted.
Getting Current Value
Retrieving Sequence Value
To retrieve the current value of a sequence without incrementing it, the currval function is used.
SELECT currval('user_id_seq');
Understanding currval
The currval function returns the last value returned by nextval for the specified sequence in the current session.
Setting Sequence Value
Manipulating Sequence Counters
To set a sequence to a specific value, the setval function is employed. This is useful for resetting or initializing sequences.
SELECT setval('user_id_seq', 1000);
Adjusting Sequence Start
The setval function sets the current value of the sequence to the specified number, and the next call to nextval will return this number plus the sequence's increment value.
Sequence Caching
Improving Performance
Sequences can be optimized using caching, which allows multiple sequence numbers to be preallocated and stored in memory, reducing disk I/O.
ALTER SEQUENCE user_id_seq CACHE 20;
Configuring Cache
The CACHE option specifies how many sequence numbers should be preallocated and stored in memory for faster access.
Sequence Ownership
Linking Sequences to Columns
A sequence can be owned by a table column, meaning it is automatically dropped when the column or table is dropped.
ALTER SEQUENCE user_id_seq OWNED BY users.id;
Ensuring Sequence Integrity
By owning a sequence to a specific column, you ensure that the sequence is tightly coupled with the lifecycle of the table and column it is associated with.
Incrementing Sequence Values
Customizing Sequence Increments
The increment value of a sequence determines the step size between successive values. By default, this is set to 1.
ALTER SEQUENCE user_id_seq INCREMENT BY 5;
Modifying Increment Steps
Changing the increment value allows for more flexible sequence number generation, suitable for specific application requirements.
Cycling Sequences
Enabling Sequence Cycling
A cycling sequence restarts from the beginning once it reaches its maximum value, allowing for repeated use of sequence numbers.
ALTER SEQUENCE user_id_seq CYCLE;
Implementing Cycling Behavior
By enabling the CYCLE option, a sequence will restart from its minimum value after reaching its maximum, useful for certain cyclic applications.
Dropping Sequences
Removing Unnecessary Sequences
Sequences that are no longer needed or have been replaced by other mechanisms can be removed from the database.
DROP SEQUENCE IF EXISTS user_id_seq;
Safe Sequence Deletion
Using the IF EXISTS clause ensures that the sequence is only dropped if it exists, preventing errors during the deletion process.
Viewing Sequence Information
Inspecting Sequence Details
PostgreSQL provides a way to view detailed information about a sequence, including its current value, increment, and more.
SELECT * FROM information_schema.sequences WHERE sequence_name = 'user_id_seq';
Accessing Sequence Metadata
Querying the information_schema.sequences view provides comprehensive metadata about any sequence in the database, aiding in management and debugging.
