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.
