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

Introduction

This is an article which is focusing on how to solve an error message appear upon inserting a new record in a table which is part of PostgreSQL database server. So, in this context there is already a running PostgreSQL database server with a database and also a table in it. For just an example, before going on into details, the following are the error occurred upon inserting a new record :

product=# insert into category(code, name) values('F&B','Food and Beverages');
ERROR: null value in column "id" of relation "table" violates not-null constraint
DETAIL: Failing row contains (null, F&B ..., Food and Beverages ...).

product=#

So, this part contains all of the details for solving and handling the error message exist as in the introduction part. There are several steps before going to the process for solving the error message :

1. The first step for solving the error message is definitely by trying to describe the database. By executing the command ‘\dtThe following is a command for describing the product :

   product=# \dt+ product;
   List of relations
   Schema  | Name             | Type  | Owner    | Persistence | Access method | Size    | Description
   --------+------------------+-------+----------+-------------+---------------+---------+-------------
   public  | category         | table | postgres | permanent   | heap          | 0 bytes |
   (1 row)
   
   product=#
   

2. Next, try to describe the table where the error message appear. In this case, it is inserting a new record to the table with the name ‘category’ but end in a failure.

   product=# \d category;
   Table "public.category"
   Column  | Type           | Collation | Nullable | Default
   --------+----------------+-----------+----------+---------
   id      | integer        |           | not null |
   kode    | character(100) |           |          |
   nama    | character(100) |           |          |
   Indexes:
   "category_pkey" PRIMARY KEY, btree (id)
   
   product=# insert into category(code, name) values('F&B','Face and Beverage');
   ERROR: null value in column "id" of relation "category" violates not-null constraint
   DETAIL: Failing row contains (null, F&B ..., Face and Beverage ...).
   product=#
   

   So, the problem is inserting the record with the column of ‘code’ and ‘name’ does not give an automation process to fill the ‘id’ column. Expecting an auto-increment feature for the primary key column with the name of ‘id’ but it does not work. An error message appear explaining that the ‘id’ column has a null value and it violates the rule of not-null constraint for the ‘id’ column. In the end, the process for inserting the new record end in a failure.

Solution

So, there are several attempt on the solution to be able to alter the column of ‘id’ in order to have an auto-increment feature. The following are those steps in sequence from a fail one into a success one. But the main key to be able to do it is just by creating a sequence. So, the sequence are in the following execution :

1. Since it is using a command line, the first step is by just running the Command Prompt. Below is the display which is appearing 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 PostgreSQL database server. The command for connecting to the PostgreSQL database server is normally using ‘psql -Uusername’ as below :

   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. Before getting the idea for a solution involving sequence, the first one is by altering the column into a serial type as follow :

   product=# alter category alter column id serial primary key;
   ERROR: syntax error at or near "category"
   LINE 1: alter category alter column id serial primary key;
   ^
   product=# alter table category alter column id serial primary key;
   ERROR: syntax error at or near "serial"
   LINE 1: alter table category alter column id serial primary ...
   ^
   product=# alter table category add column id serial primary key;
   ERROR: column "id" of relation "category" already exists
   product=# alter table category alter column id serial primary key;
   ERROR: syntax error at or near "serial"
   LINE 1: alter table category alter column id serial primary ...
   ^
   product=#
   

   The above command execution is not working. So, going on to another alternative solution is using a sequence for the column with the type of ‘primary key’. The process for creating a sequence to the column with the name ‘id’ exist as in the following process  :

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

4. After the process for creating the sequence is over, just make sure that the primary key column ‘id’ own the sequence. It is important for further usage to the column ‘id’ as in the following attempt of the query :

   alter sequence category_id_seq owned by category.id;
   ALTER SEQUENCE
   product=# \dt product;
   List of relations
   Schema  | Name             | Type  | Owner
   --------+------------------+-------+----------
   public  | category         | table | postgres
   (1 row)
   
   

5. Apparently, the process is still not working. There is something else which is currently is not exist. So it is needed as another requirement in order for the sequence can work properly as an auto-increment feature for the ‘id’ primary key column.

   product=# insert into category(code, name) values('F&B','Food and Beverages');
   ERROR: null value in column "id" of relation "table" violates not-null constraint
   DETAIL: Failing row contains (null, F&B ..., Food and Beverages ...).
   
   product=#
   

6. The following is another query command execution in order to make it working :

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

7. After executing the above query command, the auto-increment is working for the ‘id’ column primary key as follows :

   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=#