How to create PostgreSQL table with partition and fill factor

Question

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.

user330315 · Accepted Answer · 2022-10-17 21:58:23Z

4

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);

answered Oct 17, 2022 at 21:58

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create PostgreSQL table with partition and fill factor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related