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

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.
>