0

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.

    -- 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;
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:

    DELETE FROM my_table WHERE id IN (6,7,8);

    SELECT * FROM my_table;
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:

    INSERT INTO my_table (id, description)
    VALUES
    (6, 'Option F'), (7, 'Option G'), (8, 'Option H');
[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.
1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Feb 27 at 4:58

2 Answers 2

2

Fortunately, PostgreSQL error messages are quite helpful.

Following the hint shown by the PostgreSQl engine, I added the instruction "OVERRIDING SYSTEM VALUE" and it worked flawlessly:

    INSERT INTO my_table (id, description)
    OVERRIDING SYSTEM VALUE
    VALUES
    (6, 'Option F'), (7, 'Option G'), (8, 'Option H');

I hope this can be useful to any of you facing the same issue that I was.

Cheers!

0

You get the error because the column is created with "GENERATED ALWAYS".

If you use "GENERATED BY DEFAULT" instead then you can insert values into the column, and in this case it will not be generated.

1
  • 1
    Thanks for your tip, @bobflux! There is a reason for not using "GENERATED BY DEFAULT". This clause allows free insert/update into the autoincrement column, but those operations eventually cause conflicts on the trigger linked to the column. Mar 1 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.