PostgreSQL's COPY and \COPY

PostgreSQL is equivalent to a Swiss Army Knife in the database world. There are things in PostgreSQL that are very simple to use, while in another database, they take many more steps to accomplish. But sometimes, the knife has too many blades, which can cause confusion. This is one of those cases.

COPY and \COPY

I needed a quick dump of a table as part of an ETL (Extract, Transform, and Load - official definition, Exorcism, Trauma, and Lost-sleep - unofficially) to port some data. The COPY command is a quick way to output and write the data to a file.  The two copy commands below illustrate adding a delimiter and then writing the output to a table.

demo=# select * from z;
 a | b  |  c  
---+----+-----
 1 | 10 | 100
 2 | 20 | 200
 3 | 30 | 300
 4 | 40 | 400
(4 rows)
demo=# copy z to stdout (DELIMITER ',');
1,10,100
2,20,200
3,30,300
4,40,400
demo=# copy z to '/tmp/z' (DELIMITER ',');
COPY 4
demo=# 

The trick is that you must REMEMBER that the PostgreSQL server is writing the file and will need permission to write in the desired directory. If you try to write your current directory, you will probably see this:

demo=# copy z to 'z' (DELIMITER ',');
ERROR:  relative path not allowed for COPY to file
demo=# 

ETL

The table I needed to 'fix' as part of the ETL process was edited and was now ready to load. I created a new table.

demo=# create table zz (a1 int, b1 int, c1 int);
CREATE TABLE

The tricky part is that you can not use COPY to load the data. You need to use psql's \COPY! Consider \COPY a wrapper on the client side around the server's COPY command.

demo-# \copy zz from '/tmp/z/' (delimiter ',');
COPY 4
demo-# 

I find the COPY - /COPY pairing very handy.

More?


I highly recommend Wager Bianchi's COPY vs. \copy in PostgreSQL and their Security Implications for DBAs for those seeking more information on this subject and its healthy warning about security issues.


Comments

Post a Comment

Popular posts from this blog

Can Artificial Intelligence Created Better Tables Than You?

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated . But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive? I used Grok and entered the following prompt: Write the SQL to create a table on a PostgreSQL version 17 server to record data on customers. The data will include a first name, a last name, an address, a birthdate, and a unique UUID primary key. Partition the data by the year of birth. And produce some test data of at least ten records. I am sure many of us have started large projects given less instruction.  Notice: I did not denote the format for the address (US Post Office's format, or UK, or other). Nor did I set a name length. I wanted to see what assumptions were made by t...
Read more

How PostgreSQL's Aggregate FILTER Will Spoil You

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available. FILTER? To illustrate my dependency, let me start with some simple data. demo=# create table z (a int, b int, c int); CREATE TABLE demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400); INSERT 0 4 demo=# select a,b,c from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=#  If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause. What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Tabl...
Read more