# Sequences

## About

A **sequence** in PostgreSQL is a special **database object** that generates a **monotonically increasing (or decreasing)** series of unique numeric values.

It can be customized (step, min, max, etc.)

{% hint style="danger" %}
**Sequences are not rolledback in transactions**, once you call `nextval()`, the value is **gone forever**, even if your transaction fails.

This is by design — so that **sequence numbers are always unique** even under concurrency.
{% endhint %}

### Creating

```sql
CREATE SEQUENCE IF NOT EXISTS seq_name;
```

```sql
-- Specify the type of sequence (smallint | int | bigint)
CREATE SEQUENCE IF NOT EXISTS seq_name AS bigint
    START WITH 1000
    INCREMENT BY 10
    MINVALUE 1000
    MAXVALUE 1000000
    CACHE 100
    -- When the value reaches the maxvalue or minvalue it starts over
    CYCLE
;
```

### Advance sequence

```sql
SELECT nextval('seq_name');
```

### Get current value

```sql
SELECT currval('seq_name');
```

### Set sequence to specific value

```sql
SELECT setval('seq_name', value);
```

### Deleting

```sql
DROP SEQUENCE seq_name;
```

### List all sequences

```sql
SELECT relname sequence_name
FROM pg_class
WHERE
    relkind = 'S'
;
```

## Sharing Sequence

Share sequence between tables like:

```sql
CREATE SEQUENCE seq_name START WITH 100

CREATE TABLE apples (
    fruit_id INT DEFAULT nextval('seq_name') NOT NULL
)

CREATE TABLE mangoes (
    fruit_id INT DEFAULT nextval('seq_name') NOT NULL
);
```