How to correctly deal with IDENTITY fields on parent/child tables when using inheritance in PostgreSQL

Question

I have a rather simple question when playing with a PG 15.1 database.

I've tried to set up a simple inheritance case:

DROP TABLE IF EXISTS cities CASCADE;

CREATE TABLE IF NOT EXISTS cities (
  id INT UNIQUE PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "name" TEXT,
  population REAL,
  elevation INT
);

CREATE TABLE IF NOT EXISTS capitals (
  "state" CHAR(2) UNIQUE NOT NULL
) INHERITS (cities);

INSERT INTO cities (name, population, elevation) 
VALUES ('City One', 25000, 430), ('Town Two', 18000, 380), ('Urban 3', 30000, 400), ('Metropolitan 4', 50000, 450);

INSERT INTO capitals (name, population, elevation, state)
VALUES ('Capital 1', 1200000, 550, 'AK'), ('Capital 2', 1030000, 540, 'ZA');

But this raises the following error:

NOTICE:  drop cascades to table capitals
ERROR:  Failing row contains (null, Capital 1, 1.2e+06, 550, AK).null value in column "id" of relation "capitals" violates not-null constraint 

ERROR:  null value in column "id" of relation "capitals" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, Capital 1, 1.2e+06, 550, AK).

Which is OK I guess, because the doc says:

If a column in the parent table is an identity column, that property is not inherited. A column in the child table can be declared identity column if desired.

Other people met the same issue and a frequent suggestion is to use the SERIAL type instead. Which I don't really want to do.

So I also added the identity field on the child table:

DROP TABLE IF EXISTS cities CASCADE;

CREATE TABLE IF NOT EXISTS cities (
  id INT UNIQUE PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "name" TEXT,
  population REAL,
  elevation INT
);

CREATE TABLE IF NOT EXISTS capitals (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, -- added
  "state" CHAR(2) UNIQUE NOT NULL
) INHERITS (cities);

INSERT INTO cities (name, population, elevation) 
VALUES ('City One', 25000, 430), ('Town Two', 18000, 380), ('Urban 3', 30000, 400), ('Metropolitan 4', 50000, 450);

INSERT INTO capitals (name, population, elevation, state)
VALUES ('Capital 1', 1200000, 550, 'AK'), ('Capital 2', 1030000, 540, 'ZA');

This runs fine. But there is something strange now.

Indeed, this first SELECT statement seems to be OK:

SELECT * FROM capitals;
 id |   name    | population | elevation | state 
----+-----------+------------+-----------+-------
  1 | Capital 1 |    1.2e+06 |       550 | AK
  2 | Capital 2 |   1.03e+06 |       540 | ZA
(2 rows)

But this one returns multiple times the same id in the first column:

SELECT * FROM cities;
 id |      name      | population | elevation 
----+----------------+------------+-----------
  1 | City One       |      25000 |       430
  2 | Town Two       |      18000 |       380
  3 | Urban 3        |      30000 |       400
  4 | Metropolitan 4 |      50000 |       450
  1 | Capital 1      |    1.2e+06 |       550
  2 | Capital 2      |   1.03e+06 |       540
(6 rows)

So it seems that the uniqueness of the identity field of the parent table is no more respected.

I'm wondering why I met this behavior, and how could I properly deal with ids in case of inheritance in PostgreSQL? Especially, how can I keep a unique identifier on the parent table (if this makes sense)?

An underlying target for me is to have the parent table having a FK on itself, e.g. a field called twined_with to be able to link two cities as twin cities.

Answer 1

Even though it looks like the two tables are tightly connected, they are each their own, independent table. The connection is only that the inheritance child perforce has all the columns of the parent, and that a SELECT on the parent effectively performs a UNION ALL of both tables.

The way you defined the tables, each table's primary key is generated from a different sequence, hence the duplicates.

Use an explicit sequence to generate both:

CREATE SEQUENCE s;

CREATE TABLE cities (
  id INT PRIMARY KEY DEFAULT nextval('s'),
  "name" TEXT,
  population REAL,
  elevation INT
);

CREATE TABLE capitals (
  "state" CHAR(2) UNIQUE NOT NULL,
  PRIMARY KEY (id)
) INHERITS (cities);

ALTER TABLE capitals ALTER id SET DEFAULT nextval('s');

Then both primary keys will be generated from the same sequence.

Note, however, that the primary key does not enforce uniqueness across the two tables.