PostgreSQL - CREATE SEQUENCE

In PostgreSQL, a sequence is a user-defined schema-bound object that generates a sequence of integers based on the specification it was created with. Sequences are often used to generate unique IDs for rows in a table.

Here is the basic syntax for creating a sequence in PostgreSQL:

CREATE SEQUENCE sequence_name
    [ AS data_type ]
    [ START WITH start_value ]
    [ INCREMENT BY increment_value ]
    [ MINVALUE minimum_value ]
    [ MAXVALUE maximum_value ]
    [ CACHE cache_number ]
    [ CYCLE | NO CYCLE ];

Where:

sequence_name is the name of the sequence.
data_type is the type of the sequence's values. Typically, SMALLINT, INTEGER, or BIGINT. Default is BIGINT.
start_value is the first value of the sequence. Default is 1.
increment_value is the difference between two successive values in the sequence. Default is 1.
minimum_value is the minimum value for the sequence. Default is 1 for ascending sequences and -9223372036854775807 for descending ones.
maximum_value is the maximum value for the sequence. Default is 9223372036854775807 for ascending sequences and -1 for descending ones.
cache_number is the number of sequence numbers to be preallocated and stored in memory for faster access.
CYCLE specifies that the sequence continues from the start after reaching its max value. NO CYCLE is the default.

Example:

To create a sequence which starts from 100, increments by 5, and has a maximum value of 1000, you can use:

CREATE SEQUENCE my_sequence
    START WITH 100
    INCREMENT BY 5
    MAXVALUE 1000
    NO CYCLE;

After creating a sequence, you can use the nextval('sequence_name') function to get the next value from the sequence, or currval('sequence_name') to get the most recently obtained value from the sequence within the current session.

Creating auto-incrementing sequences in PostgreSQL:
- Description: Creates a new sequence for generating auto-incrementing values.
- Code:
```
CREATE SEQUENCE your_sequence;
```
Defining start value and increment for sequences in PostgreSQL:
- Description: Specifies the starting value and the increment for the sequence.
- Code:
```
CREATE SEQUENCE your_sequence
START WITH 1
INCREMENT BY 1;
```
Setting maximum and minimum values for sequences in PostgreSQL:
- Description: Limits the range of values that the sequence can generate.
- Code:
```
CREATE SEQUENCE your_sequence
MINVALUE 1
MAXVALUE 1000;
```
Caching values with CREATE SEQUENCE in PostgreSQL:
- Description: Improves performance by preallocating a specified number of sequence values.
- Code:
```
CREATE SEQUENCE your_sequence
CACHE 10;
```
Restarting sequences in PostgreSQL:
- Description: Resets the sequence to its start value.
- Code:
```
ALTER SEQUENCE your_sequence RESTART;
```
Granting permissions on sequences in PostgreSQL:
- Description: Assigns privileges to users or roles on the sequence.
- Code:
```
GRANT SELECT, USAGE ON SEQUENCE your_sequence TO your_user;
```
Viewing information about existing sequences in PostgreSQL:
- Description: Displays details about existing sequences in the database.
- Code:
```
\ds
```
Altering sequences with ALTER SEQUENCE in PostgreSQL:
- Description: Modifies the attributes of an existing sequence.
- Code:
```
ALTER SEQUENCE your_sequence
INCREMENT BY 2
MAXVALUE 10000;
```
Dropping sequences with DROP SEQUENCE in PostgreSQL:
- Description: Removes a sequence from the database.
- Code:
```
DROP SEQUENCE your_sequence;
```