Partitioning tables with a timestamp column in PostgreSQL generates ERROR-CodePudding

I have created a dummy table like:

CREATE TABLE IF NOT EXISTS TBD_partitioning_table (
    shop character varying(200),
    region character varying(200),
    sale_date date not null
) PARTITION BY LIST(sale_date);

The table should be partitioned per sale_date. When I trie to insert values on that table like:

INSERT INTO TBD_partitioning_table
VALUES ('A', 'LA', '2022-04-28'::date);

I receive the following error:

ERROR: no partition of relation "tbd_partitioning_table" found for row DETAIL: Partition key of the failing row contains (sale_date) = (2022-04-28). SQL state: 23514

Checking the official documentation of PostgreSQL I found out that I might first need to create a child table that will include the partition of the parent table like:

CREATE TABLE TBD_partitioning_table_20220428 PARTITION OF TBD_partitioning_table
    FOR VALUES FROM ('2022-04-28') TO ('2022-04-28');

Given that I will insert-append the parent table once per day with the current date, is it efficient to create a different child table each day to hold the date loaded?

Moreover, since I am not aware of an automated way to create generated table names from functions in postgreSQL, creating a different table like CREATE TABLE TBD_partitioning_table_{current_date::date} is not quite possible.

CodePudding user response：

You can create a table with variable name in a function if you use dynamic SQL with EXECUTE.

Yes, you need to create a partition before you insert data for that partition. About partitioning per day, I would consider how long the data should be retained. You should not have too many partitions, otherwise query planning will become quite slow.

CodePudding user response：

CREATE TABLE TBD_partitioning_table_20220428 PARTITION OF TBD_partitioning_table
    FOR VALUES FROM ('2022-04-28') TO ('2022-04-28');

Will not work. because it's empty range.
You can use list partition.
Demo

CREATE TABLE IF NOT EXISTS TBD_partitioning_table (
    shop text,
    region text,
    sale_date date not null
) PARTITION BY list(sale_date);

script for generating partitions.

CREATE OR REPLACE PROCEDURE generate_partitioned_tables (_begindate date, _enddate date)
    AS $$
DECLARE
    _sql text;
    loopdate date DEFAULT _begindate;
BEGIN
    IF _begindate > _enddate THEN
        RAISE EXCEPTION '_begindate should smaller than _end_date';
    END IF;
    IF _begindate is null or _enddate is null THEN
        raise exception '_begindate and _enddate cannot be null';
    end if;
    while loopdate <= _enddate LOOP
        _sql := 'CREATE TABLE TBD_partitioning_table_'
                    || to_char(loopdate, 'YYYYMMDD')
                    || ' partition of TBD_partitioning_table for values in ( '''
                    || loopdate || ''')';
        RAISE NOTICE '_sql:  %', _sql;
        EXECUTE _sql;
        SELECT
            (loopdate   interval '1 day') INTO loopdate;
    END LOOP;
END
$$
LANGUAGE plpgsql;