PostgreSQL copy command Example

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.

( COPY (SELECT * FROM country) TO ‘list_countries.copy’;).

1	( COPY (SELECT * FROM country) TO ‘list_countries.copy’;).

• 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.

We can copy the table to the file we want by using spaces as delimiter between columns with the following command.

Import file to PostgreSQL

We can transfer the data in the file to our existing table with the following command.

Export query result to file in PostgreSQL

We can export the result of a query to a file.

If we want to compress the output we will get with the program, we can use the following command.

If we want to export only 2 columns, we can use the following command.

If we want to export in binary format, we can get it as follows.

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.

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.

For example, if you have Turkish characters, you can use encoding as follows.