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.