Overview of Sequences

Private: Mastering SQL using Postgresql Creating Tables and Indexes 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 WITH
- RESTART WITH
- MINVALUE
- MAXVALUE
- CACHE
We can use functions such as nextval and currval to explicitly generate sequence numbers and also to get current sequence number in the current session.
We might have to use RESTART WITH to 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

Previous Topic

Back to Lesson

Next Topic