The invention of the "IDENTITY" column feature in PostgreSQL 10 and later versions enables the creation of columns with auto-incrementing values. However, users may encounter the "can't insert a non-default value into column id” error when working with the IDENTITY column. This error typically occurs when the IDENTITY column is defined with the "GENERATED ALWAYS" option.

This article illustrates a practical guide on fixing the "can't insert a non-default value into column id" Error in Postgres.

How to Fix the "can't insert a non-default value into column id" Error in Postgres?

The “can't insert a non-default value into column id” error arises when a user tries to insert the value to an IDENTITY column that is created using the "GENERATED ALWAYS" option. To solve this error, use the OVERRIDING SYSTEM VALUE option while inserting a user-specified value.

Follow the steps below to fix the stated error:

Step 1: Create a Table

Let’s create a new table with an IDENTITY column, as follows:

CREATE TABLE cp_example(
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
blog_name TEXT
);

The desired table has been successfully created.

Step 2: Insert Data

Now let’s insert a couple of records into the selected table using the INSERT query:

INSERT INTO cp_example(blog_name)
VALUES ('blog 1'),
('blog 2');

The given records have been inserted into the “cp_example” table:

Step 3: Verify the Table’s Data

Fetch the newly inserted records to comprehend the working of the IDENTITY column:

SELECT * FROM cp_example;

Step 4: Insert a User-specified Value in the IDENTITY Column

Now, let’s try to insert a user-specified value in the IDENTITY column and see how Postgres deals with that specific scenario:

INSERT INTO cp_example(id, blog_name)
VALUES (5, 'blog 5');

An error occurs while inserting an explicit value into the IDENTITY column:

Step 5: Use the OVERRIDING SYSTEM VALUE Option

The most convenient way to address the stated issue is to use the “OVERRIDING SYSTEM VALUE” option:

INSERT INTO cp_example( id, blog_name) 
OVERRIDING SYSTEM VALUE
VALUES (5, 'blog 5')
RETURNING *;

The below-provided output snippet shows that the stated error has been successfully rectified:

That’s all about fixing the "can't insert a non-default value into column id" error in Postgres.

Conclusion

In PostgreSQL, the “can't insert a non-default value into column id” error arises when a user tries to insert the value to an IDENTITY column that is created using the "GENERATED ALWAYS" option. To solve this error, use the OVERRIDING SYSTEM VALUE option while inserting a user-specified value. This post has presented a complete process for solving the “can't insert a non-default value into column id” error in PostgreSQL.