PostgreSQL Question posted in
The official documentation can be found here.

I can't create a date partitioned table in PostgreSQL

I am trying to create a partition table in PostgreSQL using the query below and store data by date (01~31).

CREATE TABLE test (
    aaa varchar(4) NOT NULL, 
    bbb int4 NOT NULL,
    ccc timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "PK_test" PRIMARY KEY (aaa, bbb)
) PARTITION BY RANGE(to_char(ccc, 'DD'));

CREATE TABLE test_01 PARTITION OF test FOR VALUES FROM ('01') TO ('02');
...
CREATE TABLE test_31 PARTITION OF test FOR VALUES FROM ('31') TO ('32');

However, the following error occurs in the PARTITION BY RANGE(to_char(ccc, 'DD')).

functions in partition key expression must be marked IMMUTABLE

How do I partition a table by date?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Using an expression as the partition key did not allow the use of primary key constraints. Therefore, we removed the private key constraint from the root table and added the constraint to the leaf table.

    CREATE TABLE test (
    aaa varchar(4) NOT NULL, 
    bbb int4 NOT NULL,
    ccc timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE(EXTRACT(DAY FROM ccc));

DO $$ 
BEGIN
    FOR i IN 1..31 LOOP
        EXECUTE 'CREATE TABLE test_' || i || ' PARTITION OF test FOR VALUES FROM (' || i || ') TO (' || i+1 || ')';
        EXECUTE 'ALTER TABLE test_' || i || ' ADD PRIMARY KEY (aaa, bbb)';
    END LOOP;
END $$;

  2. 0

    Use the EXTRACT() function, which is considered immutable, check below the modified PostgreSQL table partitioning to use an immutable day_of_month generated column based on the day extracted from a timestamp, enabling partitioning by day of the month.

    CREATE TABLE test (
    aaa varchar(4) NOT NULL, 
    bbb int4 NOT NULL,
    ccc timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    day_of_month int GENERATED ALWAYS AS (EXTRACT(DAY FROM ccc)) STORED,
    CONSTRAINT "PK_test" PRIMARY KEY (aaa, bbb)
) PARTITION BY RANGE(day_of_month);

CREATE TABLE test_01 PARTITION OF test FOR VALUES FROM (1) TO (2);
CREATE TABLE test_02 PARTITION OF test FOR VALUES FROM (2) TO (3);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search