How to Alter Primary Key Column as an Auto Increment Column in PostgreSQL Database Server

By Posted on

Introduction

In this article, the main purpose is to be able to alter a primary key to have an auto increment feature. The primary key column in this context exist in a database running using PostgreSQL database server. Actually, there is another table exist in a database which is running using PostgreSQL database server. But unfortunately, upon inserting a record or a row into the table, it ends in failure. It is because the column which is expected in the first place to have an auto-increment feature. But apparently, it is not and it is causing an error message to appear. The error message description is actually exist in another article for further discussion. It exist in the article with the title of ‘How to Solve Error Message ERROR: null value in column “id” of relation “table” violates not-null constraint when inserting row to a table in PostgreSQL Database Server’ in this link.

Alter Primary Key Column as an Auto Increment Column in PostgreSQL Database Server

So, in detail, this part will try to go through the steps in order to achieve the purpose. Continue on, in other words, the process in this article is showing how to alter a primary key column so it will have the auto-increment feature. Using a command line interface as Command Prompt in Microsoft Windows in this case is actual operation where the steps are in the following sequence :

  1. First of all, access the Command Prompt. The following display will appear in the Command Prompt : 

    Microsoft Windows [Version 10.0.18363.628]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\Users\Personal>

  2. Next, just connect to the PosttgreSQL database server by typing the following command normally ‘psql -Uusername’ as follows : 

    Microsoft Windows [Version 10.0.18363.628]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\Users\Personal>psql -Upostgres
Password for user postgres:
psql (14.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=#

  3. After successfully connecting to the PostgreSQL database server, just connect directly to the database by executing the command ‘\c database_name’ as exist below : 

    postgres=# \c product;
You are now connected to database "product" as user "postgres".
product=#
  4. Continuing on from the step before, just describe the table for further alter process. Actually, in this case, there is a table with the name of ‘category’. The main purpose is to change the primary key column for the category table. The following is the description of the table : 
    product=# \d category;
Table "public.category"
Column  | Type           | Collation | Nullable | Default
--------+----------------+-----------+----------+---------
id      | integer        |           | not null |
code    | character(100) |           |          |
name    | character(100) |           |          |
Indexes:
"category_pkey" PRIMARY KEY, btree (id)

  5. In order to be able  to have an auto increment feature, it need a sequence to be available. Below is the command for creating a sequence with the pattern of ‘create sequence sequence_name’  : 

    product=# create sequence category_id_seq;
CREATE SEQUENCE
product=#

  6. After creating the sequence, alter the primary key column so it will have an auto increment feature by executing the following command : 

    product=# alter table category id set default nextval('category_id_seq');
ALTER TABLE
product=#

  7. Finally,  just test it by inserting new record. The following are few samples of query for inserting new record in order to test the auto-increment feature : 

    product=# insert into category(code, name) values('F&B','Food and Beverages');
INSERT 0 1
product=# insert into category(kode, nama) values('C&W','Clothing and Wardrobe');
INSERT 0 1
product=# insert into category(kode, nama) values('M&P','Medicine and Pharmacy');
INSERT 0 1
product=#

Leave a Reply