Thread: BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)
BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)
From
PG Bug reporting form
Date:
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.
On Fri, 24 Sep 2021 at 05:14, PG Bug reporting form <noreply@postgresql.org> wrote:
> 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.
The documentation for CREATE TABLE [1] INHERITS 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.
[1] https://www.postgresql.org/docs/13/sql-createtable.html
--
Regrads,
Japin Li.
Re: BUG #17202: GENERATED BY DEFAULT AS IDENTITY not inherited (but SERIAL is)
From
Erik Huelsmann
Date:
On Fri, Sep 24, 2021 at 4:32 AM Japin Li <japinli@hotmail.com> wrote:
[ snip ]
> 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.
The documentation for CREATE TABLE [1] INHERITS 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.
[1] https://www.postgresql.org/docs/13/sql-createtable.html
Thanks for your reply! I was reading the documentation about Generated Columns [1] which says:
If a parent column is a generated column, a child column must also be a generated column using the same expression. In the definition of the child column, leave off the
GENERATED clause, as it will be copied from the parent.which I read to indicate that the identity generator in the child should have been copied from the parent (the "expression" being referred to being the implied expression of the generator function).
Concluding: It's not a bug and it's also not a functional equivalent of SERIAL in light of table inheritance.