How to create PostgreSQL table with partition and fill factor-CodePudding

I am trying to create a PostgreSQL table with partition and fillfactor and I am getting an error

CREATE TABLE public."Test1" (
    col1 int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    col2 varchar NULL,
    col3 date   null
)
partition by range (col3)
WITH (
    fillfactor=80
);

and the error is:

Error occurred during SQL query execution
Reason:
SQL Error [22023]: ERROR: unrecognized parameter "fillfactor

Able to create a PostgreSQL table with fillfactor and without partition. The version I am using is 14.

CodePudding user response：

As documented in the manual you can't specify storage parameters for partitioned tables:

Specifying these parameters for partitioned tables is not supported, but you may specify them for individual leaf partitions.

The reason is that the partitioned table does not contain any data, only the partitions will.

You can only define the fillfactor when you create the partitions:

CREATE TABLE test1 
(
  col1 int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  col2 varchar NULL,
  col3 date   null
)
partition by range (col3);

create table p1 
  partition of test1 
  FOR VALUES FROM ('2022-01-01') TO ('2022-07-01')
  WITH (fillfactor=80);