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