Skip to content

Instantly share code, notes, and snippets.

@jasonaowen
Created March 4, 2021 09:04
Show Gist options
  • Save jasonaowen/84917d3165eb7e21f2e645bb9663f659 to your computer and use it in GitHub Desktop.
Save jasonaowen/84917d3165eb7e21f2e645bb9663f659 to your computer and use it in GitHub Desktop.
Skipping sequence values in PostgreSQL
[local] owenja@owenja=> create temporary table demo (id integer primary key generated always as identity, value text);
CREATE TABLE
[local] owenja@owenja=> insert into demo (value) values ('a');
INSERT 0 1
[local] owenja@owenja=> select * from demo;
id | value
----+-------
1 | a
(1 row)
[local] owenja@owenja=> begin;
BEGIN
[local] owenja@owenja=> insert into demo (value) values ('about to be rolled back') returning id;
id
----
2
(1 row)
INSERT 0 1
[local] owenja@owenja=> rollback;
ROLLBACK
[local] owenja@owenja=> insert into demo (value) values ('about to be committed') returning id;
id
----
3
(1 row)
INSERT 0 1
[local] owenja@owenja=> select * from demo;
id | value
----+-----------------------
1 | a
3 | about to be committed
(2 rows)
[local] owenja@owenja=> alter table demo add check ((id % 2) = 1);
ALTER TABLE
[local] owenja@owenja=> insert into demo (value) values ('fails check constraint') returning id;
ERROR: 23514: new row for relation "demo" violates check constraint "demo_id_check"
DETAIL: Failing row contains (4, fails check constraint).
SCHEMA NAME: pg_temp_4
TABLE NAME: demo
CONSTRAINT NAME: demo_id_check
LOCATION: ExecConstraints, execMain.c:2003
[local] owenja@owenja=> insert into demo (value) values ('passes check constraint') returning id;
id
----
5
(1 row)
INSERT 0 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment