Working with Sequences
1. Creating Sequence
CREATE SEQUENCE order_seq
START 1000 INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807
CACHE 50 OWNED BY orders.id;
| Clause | Purpose |
|---|
| START / INCREMENT | Initial / step |
| MINVALUE / MAXVALUE | Bounds |
| CACHE n | Preallocate per backend |
| CYCLE | Restart on wraparound |
| OWNED BY | Auto-drop with column |
2. Using nextval Function
SELECT nextval('order_seq');
Note: nextval is transaction-safe but values may be skipped on rollback (gaps allowed).
3. Using currval Function
SELECT currval('order_seq'); -- last value generated in THIS session
4. Using lastval Function
INSERT INTO orders DEFAULT VALUES;
SELECT lastval(); -- last sequence touched in this session
5. Using setval Function
SELECT setval('order_seq', 1000); -- next = 1001
SELECT setval('order_seq', 1000, false); -- next = 1000
6. Creating Auto-Increment Columns
CREATE TABLE notes (
id bigserial PRIMARY KEY, -- legacy
note text
);
7. Using GENERATED ALWAYS AS IDENTITY
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
...
);
-- Insert: must use OVERRIDING SYSTEM VALUE to supply explicit id
8. Using GENERATED BY DEFAULT AS IDENTITY
CREATE TABLE messages (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
body text
);
-- Explicit id allowed; sequence used when omitted
| Mode | User Value |
|---|
| ALWAYS | Requires OVERRIDING SYSTEM VALUE |
| BY DEFAULT | Allowed |
9. Altering Sequence
ALTER SEQUENCE order_seq INCREMENT 10 RESTART WITH 5000 CACHE 100;
ALTER TABLE orders ALTER COLUMN id SET GENERATED BY DEFAULT;
10. Resetting Sequence
SELECT setval(pg_get_serial_sequence('orders','id'),
(SELECT COALESCE(max(id),0) FROM orders));
11. Setting Sequence Ownership
ALTER SEQUENCE order_seq OWNED BY orders.id; -- auto-drop with column
ALTER SEQUENCE order_seq OWNED BY NONE;
12. Listing Sequences
\ds
SELECT sequencename, last_value, increment_by
FROM pg_sequences WHERE schemaname='public';
13. Dropping Sequence
DROP SEQUENCE IF EXISTS order_seq CASCADE;