Created
March 4, 2021 09:04
-
-
Save jasonaowen/84917d3165eb7e21f2e645bb9663f659 to your computer and use it in GitHub Desktop.
Skipping sequence values in PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[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