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);

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');

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

SELECT *
FROM Email;

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

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

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;

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

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 ',';

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

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;

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

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

\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“.

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

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

• Right Click the Table Name and Select Import/Export Option from the cascading list.

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

• Under the Columns Tab of Import/Export Dialog Box, Select the Columns you want in the CSV File and Press “OK“.

• We receive a confirmation message from pgAdmin after the Successful Export of the Table.

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

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);

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');

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

SELECT *
FROM Email;

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

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

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

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

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:

• PostgreSQL DATE_PART() Function with examples
• PostgreSQL DROP TABLE + Examples
• Postgresql Having Clause
• PostgreSQL DROP COLUMN + 7 examples
• PostgreSQL INSERT Multiple Rows
• How to backup PostgreSQL database
• How to import CSV file into PostgreSQL
• Postgresql import SQL file

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