Updated May 26, 2023
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.
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 HEADEROR
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:
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 CSVOutput:
Illustrate the content of the CSV file creates after executing the above Command.
- 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 HEADEROutput:
Illustrate the content of the CSV file creates after executing the above Command.
- Copy all columns of the Person table using a query but without headers.
\copy (SELECT * FROM Person) TO 'C:\temp\Person_query.csv' WITH CSVOutput:
Illustrate the content of the CSV file creates after executing the above Command.
- 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 HEADEROutput:
Illustrate the content of the CSV file creates after executing the above Command.
- 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 CSVOutput:
Illustrate the content of the CSV file creates after executing the above Command.
- 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 HEADEROutput:
Illustrate the content of the CSV file creates after executing the above Command.
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:
Illustrate the content of the CSV file creates after executing the above Command.
- 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:
Illustrate the content of the CSV file creates after executing the above Command.
- 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:
Illustrate the content of the CSV file creates after executing the above Command.
COPY Person(first_name) TO 'C:\temp\person_first_name_header.csv' DELIMITER ',' CSV HEADER;Output:
Illustrate the content of the CSV file creates after executing the above Command.
COPY Person(last_name) TO 'C:\temp\person_last_name.csv' DELIMITER ',' CSV;Output:
Illustrate the content of the CSV file creates after executing the above Command.
COPY Person(last_name) TO 'C:\temp\person_last_name_header.csv' DELIMITER ',' CSV HEADER;Output:
Illustrate the content of the CSV file creates after executing the above Command.
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.
