How to Create a Table in PostgreSQL with an auto-increment using Sequence

By Posted on

Introduction

This article has a specific content about creating a new table. But in this case, the table has an auto-increment column. There has been a similar article regarding this matter in How to Create a Table in PostgreSQL with an auto-increment. But apparently, the content is slight different. The auto-increment column is using a serial type. But in this article, the field will use a sequence. The following are steps for creating a table in PostgreSQL database server. Furthermore, the table will have an auto-increment column using sequence :

1. Check if the PostgreSQL database server is running. An article to show how to check it is available in the PostgreSQL article page in this link. An article in the page is giving information about how to check it in Linux operating system. The article is How to Check PostgreSQL Database Server’s service status.

2. If the PostgreSQL database server is running, access the PostgreSQL command console. There are many articles in this link regarding on that matter. Those article are How to Access PostgreSQL Database Server via Command Line, ‘Connecting to PostgreSQL Database via Linux Shell Command and ‘Connect to PostgreSQL command line Linux‘.

3. After successfully accessing the PostgreSQL command console, execute the query to create a table. Below is the example of an output for accessing the PostgreSQL command console in Windows operating system : 

C:\Users\Personal>psql -Upostgres
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.
postgres=#

Create a New Sequence

1. Connect to a database. Read the article for connecting to a certain database in this page.

2. Create a new sequence. This sequence is important for the auto-increment column. Read the article How to Create a Sequence in PostgreSQL Database Server to learn how to do it. So, the execution of the query to create a new sequence exist as follow : 

mydb=# create sequence employee_id_seq;
CREATE SEQUENCE
mydb=#

3. After creating the sequence, do not forget to check if it is exist or not. The article for checking the sequence exist in How to Show or to List available Sequence in PostgreSQL Database Server . In this context, the name of the sequence is ‘mytable_id_seq’ for an example. Below is the query to check the sequence : 

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

Create a New Table

1. Finally, execute the query for creating a table . The following is the query pattern for creating a table : 

create table table_name(column_id integer not null default nextval('sequence_name_id_seq'), another_column type)

Using the above command pattern, below is the actual execution of the query for creating a new table : 

mydb=# create table mytable(id integer not null default nextval('mytable_id_seq'), myother_column varchar not null);
CREATE TABLE
mydb=#

2. Do not forget to check the new table. Read how to list table in a database from the article Display Available Tables in PostgreSQL. By executing a certain query, the list of available table in the database will appear : 

employee=# \dt
          List of relations
 Schema |   Name   | Type    |  Owner
--------+----------+---------+----------
 public | mydb     | mytable | postgres
(1 row)

3. As in the above output, there is a new table with the name of ‘mydb’. But in order to make sure that the column with the auto-increment specification using sequence is available, check the table as follows : 

employee=# \d+ mytable
                                                          Table "public.mydb"
   Column       |         Type      | Collation | Nullable |               Default                | Storage  | Stats target | Description
----------------+-------------------+-----------+----------+--------------------------------------+----------+--------------+-------------
 id             | integer           |           | not null | nextval('mytable_id_seq'::regclass)     | plain    |              |
 myother_column | character varying |           | not null |                                      | extended |              |
Access method: heap
mydb=#

To read further about how to describe or to display the detail of columns available in a table read How to Describe a table in PostgreSQL. As expected, there are two columns in the table. There is a column is the id with an auto-increment feature using sequence with the name of ‘mydb_id_seq’. The process for creating a table with an auto-increment column using sequence is a success.

Leave a Reply