Create a PostgreSQL foreign key reference with default value of NULL

Question

Consider the following table definition:

CREATE TABLE containers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id SERIAL REFERENCES containers(id)
);

When I INSERT INTO containers(name) VALUES('test_name'), I'm getting the following:

SELECT * FROM containers;
 id |   name    | parent_id
----+-----------+-----------
  1 | test_name |         1
(1 row)

It looks like the default values for parent_id is the id value of the same row. Ideally, it would be NULL. I tried to achieve this with the following:

CREATE TABLE containers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id SERIAL REFERENCES containers(id) DEFAULT NULL
);

But got the following error:

ERROR: multiple default values specified for column "parent_id" of table "containers"

Is there anything I can do to achieve what I want?

Answer 1

To ensure that the parent_id column defaults to NULL instead of the same row's id value, you need to adjust your table definition.

The issue arises because the SERIAL type automatically creates a sequence and sets a default value, which conflicts with specifying DEFAULT NULL