Re: A generated column cannot be part of a partition key

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.