PostgreSQL Export Table to CSV

In this PostgreSQL tutorial, we will learn about PostgreSQL Export Table to CSV. We can export PostgreSQL data, i.e., the Tables to CSV files in PostgreSQL using the COPY statement or \copy command.

  • PostgreSQL Export Table to CSV
  • PostgreSQL Export Table to CSV pgAdmin
  • Export Postgres Table to CSV Linux

PostgreSQL Export Table to CSV

In this section, we will learn about PostgreSQL Export Table to CSV. We can export PostgreSQL data, i.e., the Tables to CSV files in PostgreSQL using the COPY statement or \COPY command.

CSV is an abbreviation for Comma Separated Values. We can easily export a PostgreSQL table to a CSV file using COPY statement OR \COPY command. COPY statement is used for Server-Side Export and \COPY is an in-built PostgreSQL command used for Client-Side Export. We will look at various examples of PostgreSQL Export Table to CSV.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);
PostgreSQL Create Table
PostgreSQL- Create Table

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');
PostgreSQL Insert Data
PostgreSQL- Insert Data

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;
PostgreSQL Data in Table
PostgreSQL- Data in Table
  • Export Table With Header

Now we will Export a PostgreSQL Table to CSV File along with headers:

COPY Email 
TO 'E:\Email_DB.csv' 
DELIMITER ',' 
CSV HEADER;
PostgreSQL Export Table With Header
PostgreSQL- Export Table With Header

After the successful export of the table, we receive a confirmation message as COPY and the number of rows exported. In this case “COPY 10“.

PostgreSQL Email_DB CSV File
PostgreSQL- Email_DB CSV File

A CSV file is created at the specified location, in this case, “E:/Email_DB.csv” and the data is stored in a comma-separated format.

  • Export Table( Required Columns )

Now we will look at an example where we can Export a PostgreSQL Table to CSV file with some columns:

COPY Email(ID,Email) 
TO 'E:\Email_DB_2.csv' 
DELIMITER ',' 
CSV HEADER;
PostgreSQL Export Table Only Columns
PostgreSQL- Export Table( Required Columns )

TO export only some columns we need to specify the name of the columns are the name of the table. After the successful export of Email table with Columns “ID” and “Email” we receive a confirmation message “COPY 10“.

PostgreSQL Email_DB_2 CSV File
PostgreSQL- Email_DB_2 CSV File

We can see that the CSV file “Email_DB_2” contains only two columns namely “ID” and “Email” which contains all the values in a comma-separated format.

  • Export Table Without Header

Now we will Export a PostgreSQL Table to CSV File without headers:

COPY Email 
TO 'E:\Email_DB_3.csv' 
DELIMITER ',';
PostgreSQL Export Table Without Header
PostgreSQL- Export Table Without Header

If we do not want to export the Header to the CSV file then we can omit the keyword “CSV HEADER” from the COPY statement. After successful export of the PostgreSQL table to CSV File we receive a confirmation message “COPY 10“.

PostgreSQL Email_DB_3 CSV File
PostgreSQL- Email_DB_3 CSV File

We can see that the CSV File does not contain the Headers of the “Email” Table but contain all the values in a comma-separated format.

  • Export Table ( SELECT Query )

Now we will Export a PostgreSQL query to CSV file, i.e., we can also export a SELECT query to CSV file:

COPY(
SELECT * 
FROM Email 
WHERE Email ~ '[0-9]')  
TO 'E:\Email_DB_4.csv' DELIMITER ',' CSV HEADER;
PostgreSQL Export SELECT Query
PostgreSQL- Export Table ( SELECT Query )

To export a SELECT query we just need to replace the name of the table with the SELECT Query enclosed within angular brackets. After successful export of the PostgreSQL table (SELECT Query) to CSV File we receive a confirmation message “COPY 4“.

PostgreSQL Email_DB_4 CSV File
PostgreSQL- Email_DB_4 CSV File

We can see that the CSV file ” Email_DB_4 ” contains only 4 rows in a comma-separated format which satisfies the WHERE condition of the SELECT statement.

  • \COPY COMMAND

Now we will Export a PostgreSQL Table to CSV File using the \COPY command:

\COPY (SELECT * FROM Email) TO 'E:\EMAIL_DB_5.csv' WITH CSV HEADER
PostgreSQL Export Table Using \COPY Command
PostgreSQL- Export Table Using \COPY Command

\COPY is a client-side built-in PostgreSQL Command which is used to export PostgreSQL tables to CSV file. After successful export of the PostgreSQL table to CSV File we receive a confirmation message “COPY 10“.

PostgreSQL Email_DB_5 CSV File
PostgreSQL- Email_DB_5 CSV File

A CSV file is created at the specified location, in this case, “E:/Email_DB_5.csv” and the data is stored in a comma-separated format.

In this section, we learned about PostgreSQL Export Table to CSV.

Read PostgreSQL drop all tables

PostgreSQL Export Table to CSV pgAdmin

In this section, we will learn about PostgreSQL Export Table to CSV using pgAdmin. pgAdmin is a Graphic User Interface used to create, update and delete databases and tables in PostgreSQL. We can export a PostgreSQL table to CSV File in some simple steps.

First create a table using How to create table in PostgreSQL using pgAdmin and then proceed with the steps mentioned below:

  • Create a Blank text file at the location you want to export the PostgreSQL table. In this case it “E:/EMAIL_DB_6.txt”.
PostgreSQL New Text File
PostgreSQL- Create New Text File
  • Now Open pgAdmin and Select the Table you need to Export as CSV File under the Schemas section. In this case we select “Email” Table.
PostgreSQL Select Table To Export
PostgreSQL- Select Table To Export
  • Right Click the Table Name and Select Import/Export Option from the cascading list.
PostgreSQL Select Import/Export Option
PostgreSQL- Select Import/Export Option
  • In the Import/Export Dialog Box under the Options Tab:
    • Select the “Import” option using the Toggle Button against Import/Export.
    • Type the File path in the Filename Field, In our case “E:\EMAIL_DB_6.txt”.
    • Select the Format as “csv“.
    • If you want Headers in the CSV file then Toggle the Header button to “Yes“.
    • Select the Delimiter, In our case “,(Comma)”.
PostgreSQL Import Export Dialog Box
PostgreSQL- Import/Export Dialog Box
  • Under the Columns Tab of Import/Export Dialog Box, Select the Columns you want in the CSV File and Press “OK“.
PostgreSQL Import Export Dialog Box Columns
PostgreSQL- Import Export Dialog Box Columns
  • We receive a confirmation message from pgAdmin after the Successful Export of the Table.
PostgreSQL- Export Successful
PostgreSQL Email_DB_6 CSV File
PostgreSQL- Email_DB_6 CSV File

The Data of the PostgreSQL Table is transferred to the text file as comma-separated values, in this case “E:/EMAIL_DB_6.txt”.

In this section, we learned about PostgreSQL Export Table to CSV using pgAdmin.

Read Postgres RegEx – Complete tutorial

Export Postgres Table to CSV Linux

In this section, we will learn about PostgreSQL Export Table to CSV in Linux. In Linux we can export a PostgreSQL Table to CSV File using COPY statement and \COPY command. COPY statement is used for Server-Side Export and \COPY command is used for Client-Side Export.

Open Terminal window in Linux and connect to Postgres user in PostgreSQL using this code:

sudo -u postgres psql postgres
PostgreSQL Connect to Postgres User
PostgreSQL- Connect to Postgres User

Connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code:

CREATE TABLE Email(
ID SERIAL PRIMARY KEY,
Name TEXT NOT NULL,
Email TEXT);
PostgreSQL Export Create Table
PostgreSQL- Export Create Table

Now we will insert data in the table using the INSERT statement in PostgreSQL:

INSERT INTO Email(Name,Email) VALUES('James','james67@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Simona','simona@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Kiley','kiley@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Fletcher','fletcher23@spguide.com');
INSERT INTO Email(Name,Email) VALUES('Alisha','alisha@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Chanel','chanel@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Erick','erick6@sqlguide.edu');
INSERT INTO Email(Name,Email) VALUES('Amber','amber@tsinfo.edu');
INSERT INTO Email(Name,Email) VALUES('Stephen','stephen@pythonguide.com');
INSERT INTO Email(Name,Email) VALUES('Jose','jose41@spguide.com');
PostgreSQL Export Insert Data
PostgreSQL- Export Insert Data

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Email;
PostgreSQL Export Data In Table
PostgreSQL- Export Data In Table
  • SEREVR-SIDE EXPORT USING COPY STATEMENT

Now we will Export PostgreSQL Table to CSV File using the COPY Statement:

COPY Email 
TO '/home/njbleed/Documents/Email_DB_1.csv' 
DELIMITER ',' 
CSV HEADER;
PostgreSQL Export Table With Header in Linux
PostgreSQL- Export Table With Header in Linux

In Linux, we use COPY statement to Export a PostgreSQL Table to CSV File. For this, we need to provide an Absolute Path, i.e., a complete directory list required to locate the file in the system. After successful Export to CSV, we receive a confirmation message as “COPY 10“.

PostgreSQL Email_DB_1 CSV File
PostgreSQL- Email_DB_1 CSV File Linux

A CSV file is created at the specified location, in this case, “/home/njbleed/Documents/Email_DB_1.csv” and the data is stored in a comma-separated format.

  • CLIENT-SIDE EXPORT USING \COPY COMMAND

Now we will Export PostgreSQL Table to CSV File using the \COPY Command:

\COPY (SELECT * FROM Email) TO 'Documents/EMAIL_DB.csv' WITH CSV
PostgreSQL Export Table Using COPY Command in Linux
PostgreSQL- Export Table Using COPY Command in Linux

We can also use the \COPY Command to export a PostgreSQL Table to CSV File. For \COPY command, we need to provide the Relative Path of the file, i.e., the location where psql is currently saving files for your database. After successful Export to CSV we receive a confirmation message as “COPY 10“.

PostgreSQL Email_DB CSV File Linux
PostgreSQL- Email_DB CSV File Linux

A CSV file is created at the specified location, in this case, “Documents/Email_DB_1.csv” and the data is stored in a comma-separated format.

You may also like the following PostgreSQL tutorials:

In this tutorial, we have learned to Export PostgreSQL Table to CSV using different approaches. Also, we have covered these topics.

  • PostgreSQL Export Table to CSV
  • PostgreSQL Export Table to CSV pgAdmin
  • Export Postgres Table to CSV Linux