COPY command and bulk loading in PostgreSQL 12

06.2019 / Category: / Tags:

If you are relying heavily on the PostgreSQL COPY command to load data into PostgreSQL quickly, PostgreSQL 12 might offer a feature, which is most likely very beneficial to you. Bulkloading is an important operation and every improvement in this area is certainly going to help many people out there, who want to import data into PostgreSQL as fast as possible.

COPY: Loading and unloading data as fast as possible

When taking a closer look at the syntax of the COPY command in PostgreSQL 12 you will quickly see two things:

• h will now point to the correct page in the documentation
• COPY now supports a WHERE condition

Here is the complete syntax overview:

While having a link to the documentation around is certainly beneficial, the WHERE condition added to PostgreSQL 12 might even be more important. What is the purpose of this new feature? So far it was possible to completely import a file. However, in some cases this has been a problem: More often than not people only wanted to load a subset of data and had to write a ton of code to filter data before the import or once data has been written into the database already.

COPY … WHERE: Applying filters while importing data

In PostgreSQL data can be filtered while importing easily. The COPY become is pretty flexible and allows a lot of trickery. To show you, how the new WHERE clause works, I have compiled a simple example:

First of all 1000 rows are generated to make sure that we got some data to play. Then we export the content of this table to a file:

Finally, we can try to import this data again:

As you can see filtering data is pretty simple and very straight forward. One important thing to note here is: I exported an “id” column and imported it as “x”. Keep in mind that the text file does not know the data structure of our target table - you have to make sure that you filter on the column name of the table you want to import.

Old gems revisited …

If you are new to PostgreSQL in general I also want to present one of the older features, which I like a lot personally. COPY can send data to the UNIX pipe or read data from a pipe. Here is how it works:

In some cases, you might want to do more than to just export data. In this case I decided to compress the data while exporting. Before the data is imported again it is uncompressed and again filtered. As you can see it is pretty simple to combine those features in a flexible way.

If you want to learn more about PostgreSQL and loading data in general, check out our post about rules and triggers. If you want to learn more about COPY, checkout the PostgreSQL documentation.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
5 years ago

Do you know if the psql meta-command copy will receive WHERE support? It’s not listed in the draft documentation for 12, but that may be an omission.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
5 years ago
Reply to  barkingfoodog

i don't see that happen. backslash commands are a frontend thing. WHERE is a backend thing.

5 years ago

Does that mean the copy command implemented with different code than the server-side COPY routine? I always assumed copy would stream the data to the server and then invoke a COPY.

I'm wondering if there are speed advantages to use a server-side COPY beyond extra features like WHERE support.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram