BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is) - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17202
Logged by:          Erik Huelsmann
Email address:      ehuels@gmail.com
PostgreSQL version: 12.8
Operating system:   Ubuntu Linux 20.04 (running a Docker container)
Description:

While changing the definition of a parent table from the non-standard SERIAL
column type to the SQL ANSI standard "integer GENERATED BY DEFAULT AS
IDENTITY", I'm finding a difference on the resulting columns in the child
table.

With the "SERIAL" column declaration and these table definitions:

CREATE TABLE note (id serial primary key,
                   note_class integer not null references note_class(id),
                   note text not null,
                   vector tsvector not null default '',
                   created timestamp not null default now(),
                   created_by text DEFAULT SESSION_USER,
                   ref_key integer not null,
                   subject text);
 
CREATE TABLE entity_note(
      entity_id int references entity(id),
      primary key(id)) INHERITS (note);

I'm getting this output for '\d':

 
existing=# \d note
                                        Table "public.note"
   Column   |            Type             | Collation | Nullable |
  Default              
------------+-----------------------------+-----------+----------+----------------------------------
 id         | integer                     |           | not null |
nextval('note_id_seq'::regclass)
 note_class
| integer                     |           | not null | 
 note       | text                        |           | not null | 
 vector     | tsvector                    |           | not null |
''::tsvector
 created    | timestamp without time zone |           | not null | now()
 created_by | text                        |           |          |
SESSION_USER
 ref_key    | integer                     |           | not null | 
 subject    | text                        |           |          | 
Indexes:
    "note_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "note_note_class_fkey" FOREIGN KEY (note_class) REFERENCES
note_class(id)
Number of child tables: 6 (Use \d+ to list them.)
 
existing=# \d invoice_note
                                    Table "public.invoice_note"
   Column   |            Type             | Collation | Nullable |
  Default              
------------+-----------------------------+-----------+----------+----------------------------------
 id         | integer                     |           | not null |
nextval('note_id_seq'::regclass)
 note_class | integer                     |           | not null | 
 note       | text                        |           | not null | 
 vector     | tsvector                    |           | not null |
''::tsvector
 created    | timestamp without time zone |           | not null | now()
 created_by | text                        |           |          |
SESSION_USER
 ref_key    | integer                     |           | not null | 
 subject    | text                        |           |          | 
Indexes:
    "invoice_note_pkey" PRIMARY KEY, btree (id)
    "invoice_note_id_idx" btree (id)
    "invoice_note_vectors_idx" gist (vector)
Foreign-key constraints:
    "invoice_note_ref_key_fkey" FOREIGN KEY (ref_key) REFERENCES
invoice(id)
Inherits: note
 
As you can see, both the "note" and "invoice_note" tables have a default
"nextval" function applied to the "id" column. When I change "SERIAL" to
"integer generated by default AS IDENTITY primary key" as demonstrated
below, the output of '\d' changes to:

REATE TABLE note (id integer generated by default AS IDENTITY primary key,
                   note_class integer not null references note_class(id),
                   note text not null,
                   vector tsvector not null default '',
                   created timestamp not null default now(),
                   created_by text DEFAULT SESSION_USER,
                   ref_key integer not null,
                   subject text);
 
CREATE TABLE invoice_note(primary key(id)) INHERITS (note);
 
 
new=# \d note
                                        Table "public.note"
   Column   |            Type             | Collation | Nullable |
  Default              
------------+-----------------------------+-----------+----------+----------------------------------
 id         | integer                     |           | not null | generated
by default as identity
 note_class | integer                     |           | not null | 
 note       | text                        |           | not null | 
 vector     | tsvector                    |           | not null |
''::tsvector
 created    | timestamp without time zone |           | not null | now()
 created_by | text                        |           |          |
SESSION_USER
 ref_key    | integer                     |           | not null | 
 subject    | text                        |           |          | 
Indexes:
    "note_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "note_note_class_fkey" FOREIGN KEY (note_class) REFERENCES
note_class(id)
Number of child tables: 6 (Use \d+ to list them.)
 
new=# \d invoice_note
                          Table "public.invoice_note"
   Column   |            Type             | Collation | Nullable |   Default
   
------------+-----------------------------+-----------+----------+--------------
 id         | integer                     |           | not null | 
 note_class | integer                     |           | not null | 
 note       | text                        |           | not null | 
 vector     | tsvector                    |           | not null |
''::tsvector
 created    | timestamp without time zone |           | not null | now()
 created_by | text                        |           |          |
SESSION_USER
 ref_key    | integer                     |           | not null | 
 subject    | text                        |           |          | 
Indexes:
    "invoice_note_pkey" PRIMARY KEY, btree (id)
    "invoice_note_id_idx" btree (id)
    "invoice_note_vectors_idx" gist (vector)
Foreign-key constraints:
    "invoice_note_ref_key_fkey" FOREIGN KEY (ref_key) REFERENCES
invoice(id)
Inherits: note
 

Note that the "id" column of the "invoice_note" table doesn't have the
"generated by default as identity". I'm expecting the "invoice_note" table's
"id" column to have exactly the same definition as the "id" column in the
"note" table in both situations because the column isn't repeated in the
definition of the "invoice_note" definition.