How to Create a Sequence in PostgreSQL Database Server

Posted on


This article will show a specific content or material about how to create a sequence. Before creating a sequence, there is a certain definition about sequence exist in the following link. Another definition is also exist in another article available and exist via google search. Basically, in that link, a sequence in PostgreSQL is a user-defined schema-bound object that generates a sequence of integers based on a specified specification. To create a sequence in PostgreSQL, you use the CREATE SEQUENCE statement.


Create a New Sequence

There are several steps in order to be able to create a sequence in PostgreSQL database server. The following are those steps :

1. Check the PostgreSQL database server first. It has to be running. Read article about PostgreSQL in this page. In the page, there is also an article for checking the PostgreSQL database server. Specifically, it exist in How to Check PostgreSQL Database Server’s service status.

2. If the PostgreSQL database server is running. Access the PostgreSQL command console. So, before executing the command to create a sequence, access first the PostgreSQL command console as follows :

C:\Users\Personal>psql -Upostgres -dmydb
Password for user postgres:
psql (12.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.


The above example is an example in Windows operating system. Accessing PostgreSQL Database Server using postgres user and connect directly to a database with the name of ‘mydb’. Another article with the specific content for connecting or accessing PostgreSQL Database Server is available in this link. Actually, to be more specific, the article exist with the title of ‘How to Access PostgreSQL Database Server via Command Line.  The other one is  ‘Connecting to PostgreSQL Database via Linux Shell Command‘ and another one is ‘Connect to PostgreSQL command line Linux‘.

3. After successfully connect to the PostgreSQL Database Server, execute the command in the PostgreSQL command console to create sequence using the following pattern :

create sequence table_id_seq

Actually, the table_id_seq name depends on the table name. For an example, if the table name is ’employee’ then to make it simple and it is indicating a sequence, just use ’employee_id_seq’. The following is an example using the above pattern :

mydb=# create sequence mytable_id_seq;

Check if the sequence is exist after creating it in the above step. The article for displaying or listing sequence is available in How to Show or to List available Sequence in PostgreSQL Database Server. The following is a step to check for the availability of the sequence  :

mydb=# \ds
List of relations
Schema  | Name            | Type     | Owner
public  | mytable_id_seq  | sequence | postgres
(1 row)


Leave a Reply