Thread: A generated column cannot be part of a partition key
Hello,
In the documentation (https://www.postgresql.org/docs/current/ddl-generated-columns.html), there's the following statement:
A generated column cannot be part of a partition key
However, I was able to create a partitioned table using the generated column, as follows:
CREATE TABLE "partitioned_table"
(
"id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1),
"text_value" VARCHAR(100) NOT NULL,
CONSTRAINT "pk_partitioned_table" PRIMARY KEY ("id") WITH (fillfactor='100')
) PARTITION BY RANGE ("id");
CREATE TABLE "partition_table_01" PARTITION OF "partitioned_table" FOR VALUES FROM (1) TO (10000000);
CREATE TABLE "partition_table_02" PARTITION OF "partitioned_table" FOR VALUES FROM (10000000) TO (20000000);
Diego
On Tue, 23 Jul 2024 at 12:01, Diego Stammerjohann <diegostammer@gmail.com> wrote: > In the documentation (https://www.postgresql.org/docs/current/ddl-generated-columns.html), there's the following statement: > A generated column cannot be part of a partition key > However, I was able to create a partitioned table using the generated column, as follows: > CREATE TABLE "partitioned_table" > ( > "id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1), > "text_value" VARCHAR(100) NOT NULL, > CONSTRAINT "pk_partitioned_table" PRIMARY KEY ("id") WITH (fillfactor='100') > ) PARTITION BY RANGE ("id"); > > CREATE TABLE "partition_table_01" PARTITION OF "partitioned_table" FOR VALUES FROM (1) TO (10000000); > CREATE TABLE "partition_table_02" PARTITION OF "partitioned_table" FOR VALUES FROM (10000000) TO (20000000); It may benefit from better wording in the docs, but they are coherent. You did not create a generated column, but an identy column. BOTH use the keyword generated, which is probably misleading. From the docs: GENERATED ALWAYS AS ( generation_expr ) STORED This clause creates the column as a generated column. The column cannot be written to, and when read the result of the specified expression will be returned. ... GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] This clause creates the column as an identity column. It will have an implicit sequence attached to it and the column in new rows will automatically have values from the sequence assigned to it. Such a column is implicitly NOT NULL. Francisco Olarte.
OK, that sounds good to me.
So, the documentation should be update to something like this:
"An expression based generated column cannot be part of a partition key."
Sounds fair?
Diego H. S.
On Tue, Jul 23, 2024, 08:56 Francisco Olarte <folarte@peoplecall.com> wrote:
On Tue, 23 Jul 2024 at 12:01, Diego Stammerjohann
<diegostammer@gmail.com> wrote:
> In the documentation (https://www.postgresql.org/docs/current/ddl-generated-columns.html), there's the following statement:
> A generated column cannot be part of a partition key
> However, I was able to create a partitioned table using the generated column, as follows:
> CREATE TABLE "partitioned_table"
> (
> "id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1),
> "text_value" VARCHAR(100) NOT NULL,
> CONSTRAINT "pk_partitioned_table" PRIMARY KEY ("id") WITH (fillfactor='100')
> ) PARTITION BY RANGE ("id");
>
> CREATE TABLE "partition_table_01" PARTITION OF "partitioned_table" FOR VALUES FROM (1) TO (10000000);
> CREATE TABLE "partition_table_02" PARTITION OF "partitioned_table" FOR VALUES FROM (10000000) TO (20000000);
It may benefit from better wording in the docs, but they are coherent.
You did not create a generated column, but an identy column. BOTH use
the keyword generated, which is probably misleading. From the docs:
GENERATED ALWAYS AS ( generation_expr ) STORED
This clause creates the column as a generated column. The column
cannot be written to, and when read the result of the specified
expression will be returned.
...
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
This clause creates the column as an identity column. It will have an
implicit sequence attached to it and the column in new rows will
automatically have values from the sequence assigned to it. Such a
column is implicitly NOT NULL.
Francisco Olarte.
On 2024-07-23 14:01 +0200, Diego Stammerjohann wrote: > So, the documentation should be update to something like this: > > "An expression based generated column cannot be part of a partition key." > > Sounds fair? I think that change is unnecessarily confusing since that page only talks about those generated columns. Note that pg17 will also document identity columns[1], right before that generated columns section. Not sure if generated != identity should be made explicit when the preceding section introduces identity columns. [1] https://www.postgresql.org/docs/17/ddl-identity-columns.html -- Erik
Diego: On Tue, 23 Jul 2024 at 14:01, Diego Stammerjohann <diegostammer@gmail.com> wrote: > OK, that sounds good to me. Due to top posting I am unable to discern what part sounds good. > So, the documentation should be update to something like this: > "An expression based generated column cannot be part of a partition key." > Sounds fair? Sounds good, but if you analyze it there is no such thing as "An expression based generated column" defined. There are generated columns and identity columns. Unfortunately both use the keyword GENERATED in their definition / creation, probably due to some standard committee shenanigans. Francisco Olarte.
So, for me, it would be good to have in some place the information that an IDENTITY column is not a GENERATED column. That's what caused confusion to me.
Diego H. S.
On Tue, Jul 23, 2024, 10:58 Francisco Olarte <folarte@peoplecall.com> wrote:
Diego:
On Tue, 23 Jul 2024 at 14:01, Diego Stammerjohann
<diegostammer@gmail.com> wrote:
> OK, that sounds good to me.
Due to top posting I am unable to discern what part sounds good.
> So, the documentation should be update to something like this:
> "An expression based generated column cannot be part of a partition key."
> Sounds fair?
Sounds good, but if you analyze it there is no such thing as "An
expression based generated column" defined. There are generated
columns and identity columns. Unfortunately both use the keyword
GENERATED in their definition / creation, probably due to some
standard committee shenanigans.
Francisco Olarte.
On Tuesday, July 23, 2024, Diego Stammerjohann <diegostammer@gmail.com> wrote:
So, for me, it would be good to have in some place the information that an IDENTITY column is not a GENERATED column. That's what caused confusion to me.
Yeah, 5.3 defines what generated columns are and does not include identity. But nothing in Chapter 5 discusses identity which seems like a bug if Chapter 5 is intended be the official reference for concepts. Maybe we need to rename Defaults to also reference Identity?
We should point out in 5.3 that Identity is not “generated” and ensure that we link to 5.3 when we use “generated column” elsewhere in the docs.
David J.
On 23.07.24 16:18, David G. Johnston wrote: > On Tuesday, July 23, 2024, Diego Stammerjohann <diegostammer@gmail.com > <mailto:diegostammer@gmail.com>> wrote: > > So, for me, it would be good to have in some place the information > that an IDENTITY column is not a GENERATED column. That's what > caused confusion to me. > > > Yeah, 5.3 defines what generated columns are and does not include > identity. But nothing in Chapter 5 discusses identity which seems like > a bug if Chapter 5 is intended be the official reference for concepts. This has been changed in the documentation for PG 17. There are now separate sections for identity columns and generated columns: https://www.postgresql.org/docs/17/ddl-identity-columns.html https://www.postgresql.org/docs/17/ddl-generated-columns.html