(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
Last activity: Feb 27, 2024, 10:25 AM