Overview of Sequences¶
Let us go through some of the important details related to sequences.
For almost all the tables in relational databases we define primary key constraints.
Primary key is nothing but unique constraint with not null and there can be only one primary key in any given table.
Many times, we might not have appropriate column in the table which can be used as primary key. In those scenarios we will define a column which does not have any business relevant values. This is called as surrogate key.
Relational Database technologies provide sequences to support these surrogate primary keys.
In postgres we can define surrogate primary key for a given table as
SERIAL. Internally it will create a sequence.We can also pre-create a sequence and use it to populate multiple tables.
Even if we do not specify the column and value as part of the insert statement, a sequence generated number will be populated in that column.
Typically, the sequence generated number will be incremented by 1. We can change it by specifying a constant value using
INCREMENT BY.Here are some of the properties that can be set for a sequence. Most of them are self explanatory.
START WITHRESTART WITHMINVALUEMAXVALUECACHE
We can use functions such as
nextvalandcurrvalto explicitly generate sequence numbers and also to get current sequence number in the current session.We might have to use
RESTART WITHto reset the sequences after the underlying tables are populated with values in surrogate key.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
Note
Let us create a sequence which start with 101 with minimum value 101 and maximum value 1000.
%%sql
DROP SEQUENCE IF EXISTS test_seq
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE SEQUENCE test_seq
START WITH 101
MINVALUE 101
MAXVALUE 1000
INCREMENT BY 100
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql SELECT currval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
(psycopg2.errors.ObjectNotInPrerequisiteState) currval of sequence "test_seq" is not yet defined in this session
[SQL: SELECT currval('test_seq')]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
%sql SELECT nextval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| nextval |
|---|
| 101 |
%sql SELECT currval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 101 |
%sql SELECT nextval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| nextval |
|---|
| 201 |
%sql SELECT currval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 201 |
%sql SELECT nextval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| nextval |
|---|
| 301 |
%sql SELECT currval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 301 |
%%sql
ALTER SEQUENCE test_seq
INCREMENT BY 1
RESTART WITH 101
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql SELECT nextval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| nextval |
|---|
| 101 |
%sql SELECT currval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 101 |
%sql SELECT nextval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| nextval |
|---|
| 102 |
%sql SELECT currval('test_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 102 |
%sql DROP SEQUENCE test_seq
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
Note
SERIAL will make sure user_id is populated using sequence and PRIMARY KEY will enforce not null and unique constraints.
%sql DROP TABLE IF EXISTS users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql DROP SEQUENCE IF EXISTS users_user_id_seq
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
user_first_name VARCHAR(30) NOT NULL,
user_last_name VARCHAR(30) NOT NULL,
user_email_id VARCHAR(50) NOT NULL,
user_email_validated BOOLEAN,
user_password VARCHAR(200),
user_role VARCHAR(1),
is_active BOOLEAN,
created_dt DATE DEFAULT CURRENT_DATE
)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
SELECT * FROM information_schema.sequences
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
|---|---|---|---|---|---|---|---|---|---|---|---|
| itversity_retail_db | public | users_user_id_seq | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO |
%sql SELECT nextval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| nextval |
|---|
| 1 |
%sql SELECT currval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 1 |
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Donald', 'Duck', 'donald@duck.com')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%%sql
SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|---|---|---|---|---|---|---|---|
| 2 | Donald | Duck | donald@duck.com | None | None | None | None | 2020-11-23 |
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, is_active)
VALUES ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', true)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%%sql
SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
2 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|---|---|---|---|---|---|---|---|
| 2 | Donald | Duck | donald@duck.com | None | None | None | None | 2020-11-23 |
| 3 | Mickey | Mouse | mickey@mouse.com | None | None | U | True | 2020-11-23 |
%%sql
INSERT INTO users
(user_first_name, user_last_name, user_email_id, user_password, user_role, is_active)
VALUES
('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true),
('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true),
('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.
[]
%sql SELECT currval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 6 |
%sql SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|---|---|---|---|---|---|---|---|
| 2 | Donald | Duck | donald@duck.com | None | None | None | None | 2020-11-23 |
| 3 | Mickey | Mouse | mickey@mouse.com | None | None | U | True | 2020-11-23 |
| 4 | Gordan | Bradock | gbradock0@barnesandnoble.com | None | h9LAz7p7ub | U | True | 2020-11-23 |
| 5 | Tobe | Lyness | tlyness1@paginegialle.it | None | oEofndp | U | True | 2020-11-23 |
| 6 | Addie | Mesias | amesias2@twitpic.com | None | ih7Y69u56 | U | True | 2020-11-23 |
Warning
It is not a good idea to populate surrogate key fields by passing the values. Either we should specify sequence generated number or let database take care of populating the field.
%%sql
INSERT INTO users (user_id, user_first_name, user_last_name, user_email_id)
VALUES (7, 'Scott', 'Tiger', 'scott@tiger.com')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%sql SELECT currval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 6 |
Note
When data is loaded with surrogate key values into the table from external sources, it is recommended to create sequence with maximum + 1 value usingSTART WITH
%sql DROP TABLE IF EXISTS users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql DROP SEQUENCE IF EXISTS users_user_id_seq
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
Note
SERIAL will make sure user_id is populated using sequence and PRIMARY KEY will enforce not null and unique constraints.
%%sql
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
user_first_name VARCHAR(30) NOT NULL,
user_last_name VARCHAR(30) NOT NULL,
user_email_id VARCHAR(50) NOT NULL,
user_email_validated BOOLEAN,
user_password VARCHAR(200),
user_role VARCHAR(1),
is_active BOOLEAN,
created_dt DATE DEFAULT CURRENT_DATE
)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
INSERT INTO users (user_id, user_first_name, user_last_name, user_email_id)
VALUES (1, 'Donald', 'Duck', 'donald@duck.com')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%%sql
INSERT INTO users (user_id, user_first_name, user_last_name, user_email_id, user_role, is_active)
VALUES (2, 'Mickey', 'Mouse', 'mickey@mouse.com', 'U', true)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%%sql
INSERT INTO users
(user_id, user_first_name, user_last_name, user_email_id, user_password, user_role, is_active)
VALUES
(3, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true),
(4, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true),
(5, 'Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.
[]
%sql SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|---|---|---|---|---|---|---|---|
| 1 | Donald | Duck | donald@duck.com | None | None | None | None | 2020-11-23 |
| 2 | Mickey | Mouse | mickey@mouse.com | None | None | U | True | 2020-11-23 |
| 3 | Gordan | Bradock | gbradock0@barnesandnoble.com | None | h9LAz7p7ub | U | True | 2020-11-23 |
| 4 | Tobe | Lyness | tlyness1@paginegialle.it | None | oEofndp | U | True | 2020-11-23 |
| 5 | Addie | Mesias | amesias2@twitpic.com | None | ih7Y69u56 | U | True | 2020-11-23 |
%sql SELECT nextval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| nextval |
|---|
| 1 |
%sql SELECT currval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 1 |
%sql ALTER SEQUENCE users_user_id_seq RESTART WITH 5
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql SELECT currval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 1 |
%sql SELECT nextval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| nextval |
|---|
| 5 |
%sql SELECT currval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 5 |
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Scott', 'Tiger', 'scott@tiger.com')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%sql SELECT currval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 6 |
%sql SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|---|---|---|---|---|---|---|---|
| 1 | Donald | Duck | donald@duck.com | None | None | None | None | 2020-11-23 |
| 2 | Mickey | Mouse | mickey@mouse.com | None | None | U | True | 2020-11-23 |
| 3 | Gordan | Bradock | gbradock0@barnesandnoble.com | None | h9LAz7p7ub | U | True | 2020-11-23 |
| 4 | Tobe | Lyness | tlyness1@paginegialle.it | None | oEofndp | U | True | 2020-11-23 |
| 5 | Addie | Mesias | amesias2@twitpic.com | None | ih7Y69u56 | U | True | 2020-11-23 |
| 6 | Scott | Tiger | scott@tiger.com | None | None | None | None | 2020-11-23 |
%sql DROP SEQUENCE users_user_id_seq CASCADE
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|---|---|---|---|---|---|---|---|
| 1 | Donald | Duck | donald@duck.com | None | None | None | None | 2020-11-23 |
| 2 | Mickey | Mouse | mickey@mouse.com | None | None | U | True | 2020-11-23 |
| 3 | Gordan | Bradock | gbradock0@barnesandnoble.com | None | h9LAz7p7ub | U | True | 2020-11-23 |
| 4 | Tobe | Lyness | tlyness1@paginegialle.it | None | oEofndp | U | True | 2020-11-23 |
| 5 | Addie | Mesias | amesias2@twitpic.com | None | ih7Y69u56 | U | True | 2020-11-23 |
| 6 | Scott | Tiger | scott@tiger.com | None | None | None | None | 2020-11-23 |
%%sql
CREATE SEQUENCE users_user_id_seq
START WITH 7
MINVALUE 1
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
ALTER SEQUENCE users_user_id_seq
OWNED BY users.user_id
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
ALTER TABLE users
ALTER COLUMN user_id
SET DEFAULT nextval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Matt', 'Clarke', 'matt@clarke.com')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%sql SELECT * FROM users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
7 rows affected.
| user_id | user_first_name | user_last_name | user_email_id | user_email_validated | user_password | user_role | is_active | created_dt |
|---|---|---|---|---|---|---|---|---|
| 1 | Donald | Duck | donald@duck.com | None | None | None | None | 2020-11-23 |
| 2 | Mickey | Mouse | mickey@mouse.com | None | None | U | True | 2020-11-23 |
| 3 | Gordan | Bradock | gbradock0@barnesandnoble.com | None | h9LAz7p7ub | U | True | 2020-11-23 |
| 4 | Tobe | Lyness | tlyness1@paginegialle.it | None | oEofndp | U | True | 2020-11-23 |
| 5 | Addie | Mesias | amesias2@twitpic.com | None | ih7Y69u56 | U | True | 2020-11-23 |
| 6 | Scott | Tiger | scott@tiger.com | None | None | None | None | 2020-11-23 |
| 7 | Matt | Clarke | matt@clarke.com | None | None | None | None | 2020-11-23 |
%sql SELECT currval('users_user_id_seq')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| currval |
|---|
| 7 |