Skip to content

Instantly share code, notes, and snippets.

@cherts

cherts/psql_sequence.sql

Created May 29, 2025 15:14
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cherts/2185d6ea1143f2194976c8550fa36906 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cherts/2185d6ea1143f2194976c8550fa36906 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. cherts created this gist May 29, 2025.
    124 changes: 124 additions & 0 deletions psql_sequence.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,124 @@
    # Create test table
    CREATE TABLE mytable (
    id bigint not null generated by default as identity primary key,
    value1 varchar,
    value2 varchar
    );

    # Add data (bad!)
    INSERT INTO mytable(id, value1, value2) VALUES
    (1,'Daniele','Teti'),
    (2,'Peter','Parker'),
    (3,'Bruce','Banner');

    # Add data (valid)
    INSERT INTO mytable(value1, value2) VALUES ('Jake', 'The Cat');
    # but error
    # ERROR: duplicate key value violates unique constraint "mytable_pkey"
    # DETAIL: Key (id)=(1) already exists.

    # Get last max id
    WITH test1 AS (
    SELECT id AS last_value_order FROM public.mytable ORDER BY id DESC LIMIT 1
    ), test2 AS (
    SELECT last_value AS last_value_seq FROM public.mytable_id_seq
    ), test3 AS (
    SELECT max(id) AS last_value_max FROM public.mytable
    ) SELECT last_value_order, last_value_seq, last_value_max FROM test1, test2, test3;

    # Result
    +------------------+----------------+----------------+
    | last_value_order | last_value_seq | last_value_max |
    +------------------+----------------+----------------+
    | 3 | 1 | 3 |
    +------------------+----------------+----------------+
    (1 row)

    # last_value_seq <> last_value_order and last_value_max

    # Fixed
    SELECT
    'SELECT setval(pg_get_serial_sequence('''
    || pg_class.relname || ''',''' || attname || '''), (SELECT max('
    || attname || ') FROM ' || pg_class.relname || '));' sql_stm
    FROM
    pg_attribute JOIN pg_class ON pg_attribute.attrelid = pg_class.oid
    WHERE
    attnum > 0 and attidentity = 'd' AND pg_class.relname = 'mytable';

    # Exec
    SELECT setval(pg_get_serial_sequence('mytable','id'), (SELECT max(id) FROM mytable));

    # Get last max id
    WITH test1 AS (
    SELECT id AS last_value_order FROM public.mytable ORDER BY id DESC LIMIT 1
    ), test2 AS (
    SELECT last_value AS last_value_seq FROM public.mytable_id_seq
    ), test3 AS (
    SELECT max(id) AS last_value_max FROM public.mytable
    ) SELECT last_value_order, last_value_seq, last_value_max FROM test1, test2, test3;

    # Result
    +------------------+----------------+----------------+
    | last_value_order | last_value_seq | last_value_max |
    +------------------+----------------+----------------+
    | 3 | 3 | 3 |
    +------------------+----------------+----------------+

    # Good!

    # Get next id
    SELECT last_value + CASE WHEN is_called THEN 1 ELSE 0 END AS next_id FROM public.mytable_id_seq;

    # Result
    +---------+
    | next_id |
    +---------+
    | 4 |
    +---------+

    # Select all data
    SELECT * FROM public.mytable;
    +----+---------+--------+
    | id | value1 | value2 |
    +----+---------+--------+
    | 1 | Daniele | Teti |
    | 2 | Peter | Parker |
    | 3 | Bruce | Banner |
    +----+---------+--------+
    (3 rows)

    # Add 1 row
    INSERT INTO mytable(value1, value2) VALUES ('Jake', 'The Cat');

    # Good

    # Select all data
    SELECT * FROM public.mytable;
    +----+---------+---------+
    | id | value1 | value2 |
    +----+---------+---------+
    | 1 | Daniele | Teti |
    | 2 | Peter | Parker |
    | 3 | Bruce | Banner |
    | 4 | Jake | The Cat |
    +----+---------+---------+
    (4 rows)

    # Get last max id
    WITH test1 AS (
    SELECT id AS last_value_order FROM public.mytable ORDER BY id DESC LIMIT 1
    ), test2 AS (
    SELECT last_value AS last_value_seq FROM public.mytable_id_seq
    ), test3 AS (
    SELECT max(id) AS last_value_max FROM public.mytable
    ) SELECT last_value_order, last_value_seq, last_value_max FROM test1, test2, test3;

    # Result
    +------------------+----------------+----------------+
    | last_value_order | last_value_seq | last_value_max |
    +------------------+----------------+----------------+
    | 4 | 4 | 4 |
    +------------------+----------------+----------------+

    # Good and bye!