Question 394080 in PostgreSQL Question posted in
The official documentation can be found here.

Create a PostgreSQL foreign key reference with default value of NULL

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?

Tags:

2

Answers


  1. 0

    The typical use case here would be inserting a new child record with a priori knowledge of what the child’s parent is. So assuming your initial record had already been inserted, we might expect an insert along the lines of the following:

    INSERT INTO containers (name, parent_id) VALUES('sweet child of parent', 1);

    In your case, if the intention be to insert the root parent (being defined as the one record itself having no parent), then you should explicitly mention NULL as the parent_id:

    INSERT INTO containers (name, parent_id) VALUES('root', NULL);
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search