CODEWITHSUNDEEP
postgresqlpartitioningddlcreate-tabledata-partitioning
Aayush Bhatnagar
Aayush Bhatnagar

Reputation: 19

Create partitioned table using sub-query

I want to create a unlogged table based on the result of another query something like

Create table table_1
as
select * from table_2
where <<conditions>>
partition by LIST(col);

Obviously this throws an error and I am unable to find whether it is even possible in postgres or not.

I appreciate any help on this.

Edit: I know I can do it by creating the table 1st and then inserting data based on the above select statement. I am however looking for a neat way to do in 1 step.

Upvotes: -1

Views: 496

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 127436

Not possible because you have to create the child tables, the partitions, as well. But what you could do, is using LIKE to copy the structure that you need, create the partitions and then use INSERT .. SELECT .. to get the data that you need.

For performance it's better to wrap it into a single transaction.

Something like this:

BEGIN;

    CREATE TABLE IF NOT EXISTS new_table(
        LIKE foo INCLUDING ALL -- copy the entire structue
    )
    PARTITION BY RANGE(i);

    CREATE TABLE p_1_500 PARTITION OF new_table
            FOR VALUES FROM (1) TO (500);
    CREATE TABLE p_500_1000 PARTITION OF new_table
            FOR VALUES FROM (500) TO (1000);
    CREATE TABLE p_1000_1500 PARTITION OF new_table
            FOR VALUES FROM (1000) TO (MAXVALUE);
            
    INSERT INTO new_table(i)
    SELECT *
    FROM foo
    WHERE i < 500;

COMMIT; -- one large commit for all work

Upvotes: 1

Related Questions