SQL - 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.

    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.

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.

	 
    SELECT CURRVAL('my_sequence');

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

    SELECT SETVAL('my_sequence', 1);

    Drop sequence sequence name;