CODEWITHSUNDEEP
postgresqlpostgresql-copy
alt-f4
alt-f4

Reputation: 2326

Is it possible to use postgres/psql COPY into a specific partition of a table?

I am currently looking into an efficient way to allocate data into a partitioned table. Is it possible to use postgres/psql to COPY data into a specific table partition (instead of using INSERT)?

According to the documentation on COPY here:

COPY FROM can be used with plain, foreign, or partitioned tables or with views that have INSTEAD OF INSERT triggers.

And according to the documentation on partitioning here:

Be aware that COPY ignores rules. If you want to use COPY to insert data, you'll need to copy into the correct partition table rather than into the master. COPY does fire triggers, so you can use it normally if you use the trigger approach.

From my understanding of the aforementioned resources, it seems possible to copy into partition; however, I can't find any examples or support for that online.

In other words, can I write something like:

COPY some_table_partition_one FROM '/some_dir/some_file'

Upvotes: 3

Views: 6492

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246808

COPY to a partitioned table was introduced in v11:

Allow INSERT, UPDATE, and COPY on partitioned tables to properly route rows to foreign partitions (Etsuro Fujita, Amit Langote)

But COPY directly to a partition is possible in all releases since v10, where declarative partitioning was introduced.

It seems like we forgot to remove the second quotation from the documentation.

Upvotes: 6

pifor
pifor

Reputation: 7882

It is possible at least with PG 12.2:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE
CREATE TABLE measurement_y2020m03 PARTITION OF measurement
    FOR VALUES FROM ('2020-03-01') TO ('2020-03-31');
CREATE TABLE
CREATE TABLE measurement_y2020m04 PARTITION OF measurement
    FOR VALUES FROM ('2020-04-01') TO ('2020-04-30');
CREATE TABLE
insert into measurement values (1, current_date, 10,100);
INSERT 0 1
select * from measurement;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       1 | 2020-03-27 |       10 |       100
(1 row)

cat /tmp/m.dat
4,2020-04-01,40,400
copy measurement_y2020m04 from '/tmp/m.dat' delimiter ',';
COPY 1
select * from measurement;
 city_id |  logdate   | peaktemp | unitsales 
---------+------------+----------+-----------
       1 | 2020-03-27 |       10 |       100
       4 | 2020-04-01 |       40 |       400
(2 rows)

Upvotes: 3

Related Questions