PostgreSQL – SEQUENCES

A sequence is a database object used to generate unique integer values automatically. Sequences are commonly used to generate primary key values for tables, ensuring that each new row has a unique identifier. They are particularly useful in scenarios where you need to maintain a unique identifier across various tables and transactions.

Here’s how sequences work and how you can use them in PostgreSQL:

Creating a Sequence: To create a sequence, you use the CREATE SEQUENCE statement. You specify the sequence name and various options, such as the starting value, increment, and maximum value.

CREATE SEQUENCE sequence_name START WITH start_value INCREMENT BY increment_value MAXVALUE max_value MINVALUE min_value CYCLE | NO CYCLE;

For example:

CREATE SEQUENCE employee_id_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE NO CYCLE;

Using a Sequence: Once a sequence is created, you can use it to generate unique values when inserting rows into a table.

INSERT INTO table_name (column1, column2, ...) VALUES (nextval('sequence_name'), value2, ...);

For example:

INSERT INTO employees (employee_id, first_name, last_name) VALUES (nextval('employee_id_seq'), 'John', 'Doe');

Getting the Next Value: You can use the nextval() function to retrieve the next value from a sequence without actually inserting it into a table.

SELECT nextval('sequence_name');

Resetting a Sequence: To reset a sequence to a specific value, you can use the SETVAL function.

SELECT setval('sequence_name', new_value);

Dropping a Sequence: To remove a sequence, you use the DROP SEQUENCE statement.

DROP SEQUENCE sequence_name;

Guidelines for Sequence Usage:

Sequences are typically used for generating primary keys, but they can also be used to generate other unique identifiers.

It’s important to manage and maintain sequences carefully to avoid gaps or conflicts in generated values, especially in scenarios with concurrent transactions.

Sequences can be shared among multiple tables, ensuring consistency in primary key generation.

Sequences provide a reliable and efficient way to generate unique integer values, making them a valuable tool for managing primary keys and ensuring data integrity in PostgreSQL databases.