Sample Header Ad - 728x90

(PostgreSQL) Insert a tuple containing specific value in a column linked to a sequence

0 votes
2 answers
242 views
I started writing this post as a question but, while reproducing the error, I found the solution in the PostgreSQL documentation. So I'll share the solution here for the community. A PostgreSQL 14 table had some data deleted accidentally.
sql
    -- Creating table
    CREATE TABLE my_table (
        id            integer      not null  primary key  generated always as identity,
        description   varchar(50)  not null
    );

    -- Filling data
    INSERT INTO my_table (description)
    VALUES
    ('Option A'), ('Option B'), ('Option C'), ('Option D'), ('Option E'),
    ('Option F'), ('Option G'), ('Option H'), ('Option I'), ('Option J');

    SELECT * FROM my_table;
text
id  | description
----+------------
1   | Option A
2   | Option B
3   | Option C
4   | Option D
5   | Option E
6   | Option F
7   | Option G
8   | Option H
9   | Option I
10  | Option J
Accidental deletion:
sql
    DELETE FROM my_table WHERE id IN (6,7,8);

    SELECT * FROM my_table;
text
id  | description
----+------------
1   | Option A
2   | Option B
3   | Option C
4   | Option D
5   | Option E
9   | Option I
10  | Option J
In order to reinsert those rows back into the table, I tried to run the following instruction, explicitly defining values for the "id" column, but it raised an exception:
sql
    INSERT INTO my_table (id, description)
    VALUES
    (6, 'Option F'), (7, 'Option G'), (8, 'Option H');
text
[428C9] ERROR: cannot insert a non-DEFAULT value into column "id" Detail: Column "id" is an identity column defined as GENERATED ALWAYS. Hint: Use OVERRIDING SYSTEM VALUE to override.
Asked by ValerioOliveira (41 rep)
Feb 27, 2024, 03:16 AM
Last activity: Feb 27, 2024, 10:25 AM