PostgreSQL - Export PostgreSQL Table to CSV file
Last Updated :
23 Nov, 2022
In this article we will discuss the process of exporting a PostgreSQL Table to a CSV file. Here we will see how to export on the server and also on the client machine.
For Server-Side Export:
Use the below syntax to copy a PostgreSQL table from the server itself:
Syntax: COPY Table_Name TO 'Path/filename.csv' CSV HEADER;
Note: If you have permission to perform a read/write operation on the server-side then use this command.
Example:
First, let’s create a table with columns id, first_name, last_name, and email to the database:
CREATE TABLE students(
id SERIAL PRIMARY KEY,
first_name VARCHAR,
last_name VARCHAR,
email VARCHAR UNIQUE
);
Let’s insert some data into our students table:
INSERT INTO students(first_name, last_name, email)
VALUES('Virender', 'Sehwag', '[email protected]'),
('Hardik', 'Pandiya', '[email protected]'),
('Shreyas', 'Iyer', '[email protected]'),
('Rishabh', 'Pant', '[email protected]');
Now check the data in the table:
SELECT * FROM students;
Output:
Now export the above table as a CSV file.
COPY students TO '/tmp/student1.csv' CSV HEADER;
Note: Make sure that the path that you specify should have read/write permission.
If everything works fine then it should look like this:
The CSV file would look like below:
We can also specify columns that we want to export or write a query for the data.
COPY (SELECT first_name FROM students) TO '/tmp/student.csv' CSV HEADER;
Output:
The CSV file would look like below:
CSV FileFor Client-Side Export:
Use the below syntax for client-side export of CSV file:
Syntax: \copy Table_Name to 'Path/filename.csv' CSV HEADER
If you do not have permission to perform a read/write operation on the server-side and want to copy the table to the client-side machine then use this command.
Let's use the students table here also.
Execute the below command to export the table to a CSV file.
\copy students to '/tmp/students.csv' CSV HEADER
Output:
The CSV file would look like below:
You can give a query to select data here also.
Similar Reads
Export SQL Server Data From Table to CSV File SQL Server is a very popular relational database because of its versatility in exporting data in Excel, CSV, and JSON formats. This feature helps with the portability of data across multiple databases. Here, we will learn how to export SQL Server Data from a table to a CSV file. Tools like Azure Dat
3 min read
PostgreSQL - Import CSV File Into Table Importing data from a CSV (Comma-Separated Values) file into a PostgreSQL database is a common task for database administrators and developers. PostgreSQL provides the COPY command, which is a powerful and efficient way to bulk load data directly from a CSV file into a table. In this article, we wil
2 min read
How to Export SQL Server Data to a CSV File? Here we will see, how to export SQL Server Data to CSV file by using the 'Import and Export wizard' of SQL Server Management Studio (SSMS). CSV (Comma-separated values): It is a file that consists of plain text data in which data is separated using comma(,). It is also known as Comma Delimited Files
2 min read
PostgreSQL - CREATE TABLE AS The CREATE TABLE AS statement in PostgreSQL is a powerful tool used to create a new table and populate it with data returned by a query. This functionality allows you to generate tables on the fly based on query results, which can be very useful for reporting, analysis, and other tasks.Let us better
3 min read
PostgreSQL - Size of a Table PostgreSQL provides a variety of functions to help you query the size of your tables. We'll focus on the 'pg_relation_size()' function to get the size of a table and enhance the readability of the output using the 'pg_size_pretty()' function. In this article, we will be using a sample database for r
3 min read
PostgreSQL - CREATE TABLE In PostgreSQL, the CREATE TABLE statement is used to define a new table within a database. It allows us to specify the table's structure, including column names, data types, and constraints, ensuring data integrity and consistency. Understanding the PostgreSQL table creation process is essential for
5 min read
How to import and export data using CSV files in PostgreSQL In this article, we are going to see how to import and export data using CSV file in PostgreSQL, the data in CSV files can be easily imported and exported using PostgreSQL. To create a CSV file, open any text editor (notepad, vim, atom). Write the column names in the first line. Add row values separ
3 min read
Python - Import CSV into PostgreSQL In this article, we will see how to import CSV files into PostgreSQL using the Python package psycopg2. First, we import the psycopg2 package and establish a connection to a PostgreSQL database using the pyscopg2.connect() method. before importing a CSV file we need to create a table. In the example
2 min read
How to Insert Multiple Rows to a Table in PostgreSQL? Inserting multiple rows into a table in PostgreSQL is a common and efficient operation, especially when handling large datasets. By executing a single SQL query, multiple rows can be added simultaneously, reducing overhead and enhancing performance. This method is particularly valuable in scenarios
5 min read
PostgreSQL - Connect To PostgreSQL Database Server in Python The psycopg database adapter is used to connect with PostgreSQL database server through python. Installing psycopg: First, use the following command line from the terminal: pip install psycopg If you have downloaded the source package into your computer, you can use the setup.py as follows: python s
4 min read