This article contains information about PostgreSQL copy command example such as exporting query result to csv, importing file to postgres.
What is PostgreSQL copy command?
- The COPY command moves data between PostgreSQL tables and standard file system files.
- COPY TO copies the contents of the table to the file.
- COPY TO can also copy the results of the SELECT query. That is, if the column list is specified, COPY TO only copies the data in the specified columns to the file.
- The COPY command instructs the PostgreSQL server to read from or write to the file directly. Therefore, the file must be accessible to the PostgreSQL user.
- COPY FROM copies the data from the file to the table.
- When using the COPY FROM command, each field in the file is inserted sequentially to the specified column. Table columns not specified in the COPY FROM column list get their default values.
- It is necessary to grant SELECT privilege on the table read by COPY TO, and the INSERT privilege in the table where the values are inserted with COPY FROM.
- COPY TO can only be used with tables, not views. However, if we want to copy the contents of the view, we must feed the COPY command with the sql query.
- Files named in the COPY command are read or written directly by the server, not by the client application. Therefore, it must be located on or accessible to the database server machine, not the client either.
- We shouldn’t confuse COPY with \copy in psql. \copy calls COPY FROM STDIN or COPY TO STDOUT and then retrieves and stores the data from a file accessible by the psql client. Therefore, file accessibility and access rights depend on the client rather than the server when using \copy.
PostgreSQL copy command Example
Export table in PostgreSQL
We can copy the table to the client by using the vertical bar (|) as a delimiter between columns with the following command.
|
1 |
COPY country TO STDOUT (DELIMITER '|'); |
We can copy the table to the file we want by using spaces as delimiter between columns with the following command.
|
1 |
COPY country TO '/database/data/test_data.copy' (DELIMITER ' '); |
Import file to PostgreSQL
We can transfer the data in the file to our existing table with the following command.
|
1 |
COPY country FROM '/database/data/test_data.copy' (DELIMITER ' '); |
Export query result to file in PostgreSQL
We can export the result of a query to a file.
|
1 |
COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/database/data/test_data.copy'; |
If we want to compress the output we will get with the program, we can use the following command.
|
1 |
COPY country TO PROGRAM 'gzip > /database/data/test_data.copy.gz'; |
If we want to export only 2 columns, we can use the following command.
|
1 |
COPY country(col, col2) TO '/database/data/test_data.copy' DELIMITER ' '; |
If we want to export in binary format, we can get it as follows.
|
1 |
COPY country TO STDOUT WITH BINARY; |
If we want to export in CSV format, we can get it as follows. If you want you can export query result to csv in postgres in this way.
|
1 |
COPY country TO STDOUT WITH CSV; |
There are a few basic terms we need to know about CSV files;
DELIMITER – Delimiter is a character that separates each row of the file into columns; In the CSV file, the delimiter is comma.
HEADER – When a CSV file is created, the header row is the first line of the file containing the column names. If you don’t want column names to be copied to the CSV file, you can ignore HEADER.
|
1 |
COPY country FROM '/database/data/test_data.copy' WITH delimiter ',' CSV HEADER; |
For example, if you have Turkish characters, you can use encoding as follows.
|
1 |
COPY country FROM '/database/data/test_data.copy' WITH delimiter ',' CSV HEADER encoding 'WIN1254'; |
