Continuation of Postgress Sql (Sequence )

In the last blog we have seen the creation of table and we came across the figure

So in the above figures we are seeing an extra table is created registrations_registration_id_seq

In the above figure in the default section we are seeing nextval() and also PRIMARY KEY, btree(registration_id)

In this blog we will be discussing in some detail so that we could get some brief understanding about sequence and btree

In the last blog we have created a table registrations in that there is an attribute registrations_id which is of big serial datatype which is similar to autoincrement in the MySQL.

Sequence is a number generator in the postgress sql. when we try to insert a record in the database it takes the value from nextval(). By default it starts from ‘1’.

So if we want to create a user defined sequence, the command is

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

Temp: creating temporary sequence . So if we create temporary sequence the sequence will last only for that session. When we logout from the session the sequence automatically gets removed from the database.

INCREMENT: by how much number we should increment for the next record. By default it is 1. So this means if the current record value is 10 and in our command INCREMENT 20. then the next record will have value 15. Increment value can be negative also. If increment value is positive then the column values will be ascending in nature. If increment value is negative then the column values will be descending in nature

MINVALUE: You can specify the min value from where it should start if our column values be in ascending. If we dont specify then default min value is taken.

MAXVALUE:You can specify the max value from where it should start if our column values be in descending order. If we dont specify then default max value is taken.

OWNED BY: In general we create sequences so that it can be used by the tables. Here we can specify this sequence should be used by which table and for which column should this sequence should get applied

References: https://www.postgresql.org/docs/9.5/sql-createsequence.html

https://www.postgresqltutorial.com/postgresql-sequences/