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

Question

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'

Laurenz Albe · Answer 1 · 2021-10-27T03:17:36.653

4

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.

edited Oct 27 '21 at 03:17

answered Mar 27 '20 at 10:46

Laurenz Albe

209,280
17
206
263

1

The second quote is still present here in v11 doc though: https://www.postgresql.org/docs/11/ddl-partitioning.html – Jan Tomášek Oct 26 '21 at 11:07
@JanTomášek You are right; I have fixed the answer. – Laurenz Albe Oct 27 '21 at 03:17

pifor · Accepted Answer · 2020-03-27T10:57:21.357

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)

Adding to what you have already said, I have tested your code, and found that COPY even works without specifying the exact partition to insert into. For example: ```copy measurement_y2020m04 from '/tmp/m.dat' delimiter ',';``` will automatically allocate data to relevant partition. PostgreSQL 12.2 on x86_64-apple-darwin19.3.0 — alt-f4, Mar 28 '20 at 18:58

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

2 Answers2