SEQUENCE

In PostgreSQL, a sequence is a database object that generates a sequence of unique integers. Sequences are often used to generate unique primary key values for tables.
Here's how you can create and use a sequence in PostgreSQL.

Creating a Sequence :
To create a sequence, you can use the CREATE SEQUENCE statement. Example:

    CREATE SEQUENCE my_sequence
        START 1
        INCREMENT 1
        MINVALUE 1
        MAXVALUE 1000
        CACHE 10;

This example creates a sequence named my_sequence that starts at 1, increments by 1, has a minimum value of 1, a maximum value of 1000, and caches 10 values for better performance.

Using a Sequence to Generate Values :
You can use the NEXTVAL function to get the next value from a sequence. Here's an example of using a sequence to generate a unique identifier for a table:

    CREATE TABLE my_table (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255)
    );

    INSERT INTO my_table (name) VALUES ('John');
    INSERT INTO my_table (name) VALUES ('Jane');

The 'id' column will be automatically populated with unique values from the sequence.
In this example, the id column is defined as SERIAL, which is a shorthand for creating an integer column and a associated sequence. When you insert values into the table, PostgreSQL will automatically generate unique values for the id column.

Getting the Current Value of a Sequence :
You can use the CURRVAL function to get the current value of a sequence:


    SELECT CURRVAL('my_sequence');

Resetting a Sequence :
If you want to reset the sequence to its start value, you can use the SETVAL function:


    SELECT SETVAL('my_sequence', 1);

Dropping a sequence :


     Drop sequence sequence name;