EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Export CSV
 

PostgreSQL Export CSV

Updated May 26, 2023

PostgreSQL Export CSV

 

 

Definition of PostgreSQL Export CSV

PostgreSQL allows us to export the content or data of the table or query to the CSV file. The CSV is an abbreviation for the comma-separated file format. PostgreSQL supports various commands for exporting the data to the comma-separated file format. We can export the content of the table as well as the result of the query to the comma-separated file format. The feature to export the data to the comma-separated file format supported by the PostgreSQL is very helpful when we have to transfer the data from one database application to another database application or if we have to transfer the tables to the different systems.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Consider the following syntax to understand the commands for exporting the table or the query result to CSV in PostgreSQL.

\copy [table_name/SQL_query] to '[Relative_Path/file_name.csv]' DELIMITER ',' CSV HEADER

OR

COPY [table_name/SQL_query] to '[Absolute_Path/file_name.csv]' DELIMITER ',' CSV HEADER;

Explanation

  • file_name.csv: This defines the name with the extension for the file, and it should be a CSV file.
  •  HEADER: This defines that the command copy should include the headers while exporting.

How does PostgreSQL Export CSV work in PostgreSQL?

  • We can export the PostgreSQL table or the query result set to the comma-separated files by using \copy and COPY commands.
  • The extension of the file used in the COPY command or \copy command should be. CSV.
  • We can export the PostgreSQL table or the query result set with or without the headers.

Examples of PostgreSQL Export CSV

Consider the following CREATE TABLE statement, which will create a table named ‘Person’.

CREATE TABLE Person(
id serial PRIMARY KEY,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL
);

Now, we will insert a row into the Person table by using the INSERT INTO statement as follows:

INSERT INTO Person(first_name, last_name)
VALUES
('Jacob', 'Smith '),
('Michael', 'Petter'),
('Chris', 'Johnson '),
('Mike', 'Brown'),
('Mark', 'Williams'),
('Daniel', 'Lopez'),
('David', 'Bravo');

Illustrate the content of the Person table by using the following SQL statement and a snapshot.

SELECT * FROM Person;

Output:

PostgreSQL Export CSV-1.1

Now we will understand the export to CSV in detail by using the \copy and COPY command as follows.

1. \copy command

Consider the following example, where we export all data from the Person table.

  • Copy the full table content of the Person table using \copy command without headers.
\copy Person TO 'C:\temp\Person_table.csv' WITH CSV

Output:

PostgreSQL Export CSV-1.2

Illustrate the content of the CSV file creates after executing the above Command.

PostgreSQL Export CSV-1.3

  • Copy the full table content of the Person table using \copy command with headers.
\copy Person to 'C:\temp\Person_table_header.csv' WITH CSV HEADER

Output:

PostgreSQL Export CSV-1.4

Illustrate the content of the CSV file creates after executing the above Command.

PostgreSQL Export CSV-1.5

  • Copy all columns of the Person table using a query but without headers.
\copy (SELECT * FROM Person) TO 'C:\temp\Person_query.csv' WITH CSV

Output:

PostgreSQL Export CSV-1.6

Illustrate the content of the CSV file creates after executing the above Command.

PostgreSQL Export CSV-1.7

  • Copy all columns of the Person table using a query and with headers.
\copy (SELECT * FROM Person) TO 'C:\temp\Person_query_header.csv' WITH CSV HEADER

Output:

PostgreSQL Export CSV-1.8

Illustrate the content of the CSV file creates after executing the above Command.

PostgreSQL Export CSV-1.9

  • Copy partial columns of the Person table using a query but without headers.
\copy (SELECT first_name FROM Person) TO 'C:\temp\Person_partial.csv' WITH CSV

Output:

PostgreSQL Export CSV-1.10

Illustrate the content of the CSV file creates after executing the above Command.

PostgreSQL Export CSV-1.11

  • Copy partial columns of the Person table using a query and with headers.
\copy (SELECT first_name FROM Person) TO 'C:\temp\Person_partial_header.csv' WITH CSV HEADER

Output:

PostgreSQL Export CSV-1.12...

Illustrate the content of the CSV file creates after executing the above Command.

PostgreSQL Export CSV-1.13

2. COPY Command

  • Copy the full table content of the Person table using COPY command without headers.
COPY Person TO 'C:\temp\person_full.csv' DELIMITER ',' CSV;

Output:

PostgreSQL Export CSV-2.1

Illustrate the content of the CSV file creates after executing the above Command.

PostgreSQL Export CSV-2.2

  • Copy the full table content of the Person table using COPY command with headers.
COPY Person TO 'C:\temp\person_full_ header.csv' DELIMITER ',' CSV HEADER;

Output:

Output-2.3

Illustrate the content of the CSV file creates after executing the above Command.

Output-2.4

  • Copy the partial content of the Person table using COPY command with headers.
COPY Person(first_name) TO 'C:\temp\person_first_name.csv' DELIMITER ',' CSV;

Output:

Output-2.5

Illustrate the content of the CSV file creates after executing the above Command.

Output-2.6

COPY Person(first_name) TO 'C:\temp\person_first_name_header.csv' DELIMITER ',' CSV HEADER;

Output:

Output-2.7

Illustrate the content of the CSV file creates after executing the above Command.

PostgreSQL Export CSV-2.8

COPY Person(last_name) TO 'C:\temp\person_last_name.csv' DELIMITER ',' CSV;

Output:

Output-2.9

Illustrate the content of the CSV file creates after executing the above Command.

Output-2.10

COPY Person(last_name) TO 'C:\temp\person_last_name_header.csv' DELIMITER ',' CSV HEADER;

Output:

Output-2.11

Illustrate the content of the CSV file creates after executing the above Command.

Output-2.12

Conclusion

We hope from the above article; you have understood how to export the PostgreSQL table or result in a set of the query to the comma-separated file format and how the export of the PostgreSQL table or result in a set of the query to the comma-separated file format work. Also, we have added several examples of the export of the PostgreSQL table or result set of the query to the comma-separated file format to understand it in detail.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Export CSV” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL EXCEPT
  2. VARCHAR in PostgreSQL
  3. PostgreSQL Materialized Views
  4. PostgreSQL Average

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW