In database management, we often need to transfer data between PostgreSQL and external files.
For example, a university may need to:
PostgreSQL supports importing and exporting data using CSV, text files, SQL commands, and graphical tools such as pgAdmin.
Data import means transferring data from an external file into a database table.
For example:
Suppose we have a CSV file containing student records:
students.csv
student_id,student_name,department,marks
1,Ali,Computer Science,85
2,Sara,Information Technology,92
3,Ahmed,Mathematics,78
4,Hina,Physics,88
We can import this information into a PostgreSQL table.
Data export means transferring data from a database table or query into an external file.
For example, we can export student records from PostgreSQL into a CSV file.
The exported file can then be opened in:
Before importing data, create a table in PostgreSQL.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department VARCHAR(100),
marks NUMERIC(5,2)
);
This table contains four columns:
| Column | Data type | Purpose |
|---|---|---|
| student_id | INT | Student ID |
| student_name | VARCHAR(50) | Student name |
| department | VARCHAR(100) | Department name |
| marks | NUMERIC(5,2) | Student marks |
PostgreSQL provides the COPY command to import data from a file into a table.
Syntax:
COPY table_name
FROM 'file_path'
WITH (
FORMAT CSV,
HEADER TRUE,
DELIMITER ','
);
Explanation:
COPY: Transfers data between a file and a table.table_name: Destination table.FROM: Specifies the source file.FORMAT CSV: Specifies CSV format.HEADER TRUE: Skips the first row containing column names.DELIMITER ',': Specifies a comma as the separator.Example:
COPY students
FROM '/path/to/students.csv'
WITH (
FORMAT CSV,
HEADER TRUE,
DELIMITER ','
);
Replace the example path with the actual file path on the PostgreSQL server.
Important: SQL COPY reads the file from the database server’s file system, not necessarily from your own computer.
Consider the following CSV file:
student_id,student_name,department,marks
1,Ali,Computer Science,85
2,Sara,Information Technology,92
The first row contains column names, not actual student records.
When we use:
HEADER TRUE
PostgreSQL skips the first row.
Without this option, PostgreSQL may try to insert the column names as data, causing a data conversion error.
The \copy command is useful when the CSV file is stored on your own computer rather than the database server.
Syntax:
\copy table_name FROM 'file_path' WITH (FORMAT CSV, HEADER TRUE);
Example:
\copy students FROM 'C:/Data/students.csv' WITH (FORMAT CSV, HEADER TRUE);
Here:
\copy is a psql client command.Important: \copy is not an SQL statement. It is a command available in the psql terminal.
pgAdmin provides a graphical interface for importing CSV files.
How to Import CSV into PostgreSQL: COPY Command, pgAdmin, and Automated Pipelines
Import/Export Data Dialog — pgAdmin 4 9.12 documentation
How to Import or Export CSVs to PostgreSQL Using pgAdmin — CommandPrompt Inc.
6
Follow these steps:
Schemas folder.public schema.Tables folder.After importing, execute:
SELECT * FROM students;
Verify that the records have been imported correctly.
PostgreSQL also allows us to export table data into a CSV file.
Syntax:
COPY table_name
TO 'file_path'
WITH (
FORMAT CSV,
HEADER TRUE,
DELIMITER ','
);
Example:
COPY students
TO '/path/to/students_export.csv'
WITH (
FORMAT CSV,
HEADER TRUE,
DELIMITER ','
);
This exports all records from the students table into a CSV file.
The exported file will contain column names in the first row.
We can also export data to a CSV file on our own computer using \copy.
Example:
\copy students TO 'C:/Data/students_export.csv' WITH (FORMAT CSV, HEADER TRUE);
The file will be created on the computer running psql.
This is useful when you do not have direct access to the database server’s file system.
Sometimes, we do not need to export all columns.
We can export only the required columns using a query.
Example:
COPY (
SELECT student_id, student_name, marks
FROM students
)
TO '/path/to/student_marks.csv'
WITH (
FORMAT CSV,
HEADER TRUE
);
This exports only:
We can also export records that meet specific conditions.
Example: Export students who obtained 80 or more marks.
COPY (
SELECT *
FROM students
WHERE marks >= 80
)
TO '/path/to/high_achievers.csv'
WITH (
FORMAT CSV,
HEADER TRUE
);
Only students with marks greater than or equal to 80 will be exported.
We can combine ORDER BY with the export query.
Example: Export students in descending order of marks.
COPY (
SELECT *
FROM students
ORDER BY marks DESC
)
TO '/path/to/sorted_students.csv'
WITH (
FORMAT CSV,
HEADER TRUE
);
Sometimes, a CSV file contains only a few columns of a table.
We can specify the destination columns in the COPY command.
Example:
COPY students (
student_id,
student_name,
department
)
FROM '/path/to/students.csv'
WITH (
FORMAT CSV,
HEADER TRUE
);
In this example, the CSV file must contain values corresponding to the three specified columns.
The marks column must have a default value or allow NULL; otherwise, the import may fail.
CSV files normally use commas to separate values.
However, some files use:
;)|)We can specify a different delimiter.
Example:
Suppose the file contains:
student_id;student_name;department;marks
1;Ali;Computer Science;85
2;Sara;Information Technology;92
Import it using:
COPY students
FROM '/path/to/students.csv'
WITH (
FORMAT CSV,
HEADER TRUE,
DELIMITER ';'
);
PostgreSQL also supports text format.
Example:
COPY students
TO '/path/to/students.txt'
WITH (
FORMAT TEXT,
DELIMITER '|'
);
This exports records using the pipe symbol as the separator.
Example output:
1|Ali|Computer Science|85
2|Sara|Information Technology|92
Sometimes, a CSV file contains empty values.
Example:
student_id,student_name,department,marks
1,Ali,Computer Science,85
2,Sara,Information Technology,
By default, an unquoted empty field in CSV input represents NULL.
We can also specify a NULL representation.
Example:
COPY students
FROM '/path/to/students.csv'
WITH (
FORMAT CSV,
HEADER TRUE,
NULL 'NULL'
);
In this case, a field containing the unquoted text NULL is interpreted as a SQL NULL value.
Both commands transfer data between files and PostgreSQL tables, but they work differently.
| Feature | COPY | \copy |
|---|---|---|
| Type | SQL command | psql command |
| File location | Database server | Client computer |
| Execution | PostgreSQL server | psql client |
| File permissions | Server permissions | Client permissions |
| Use | Server-side file transfer | Local file transfer |
Remember:
COPY when the file is accessible to the PostgreSQL server.\copy when the file is on the computer running psql.Reason: The PostgreSQL server does not have permission to read or write the specified file.
Solution: Check the file permissions or use \copy from psql.
Reason: The specified file path is incorrect or inaccessible.
Solution: Verify the file path and location.
Reason: The CSV value does not match the destination column’s data type.
For example, inserting ABC into an integer column.
Solution: Check the CSV data and column data types.
Reason: The imported file contains a student ID that already exists in the table.
Solution: Check for duplicate records before importing.
Reason: The CSV file contains a different number of columns than the specified destination columns.
Solution: Check the CSV structure and the COPY column list.
COPY is a PostgreSQL SQL command.\copy is a psql client command.HEADER TRUE skips the first row during CSV import.DELIMITER specifies the separator.COPY TO exports data.COPY FROM imports data.Exercise 1: Create a table named employee with the following columns:
Exercise 2: Create a CSV file containing five employee records.
Exercise 3: Import the CSV file into the employee table using pgAdmin.
Exercise 4: Display all imported employee records.
Exercise 5: Export all employee records into a CSV file.
Exercise 6: Export only employee names and departments.
Exercise 7: Export employees whose salary is greater than 50,000.
Exercise 8: Export employee records in descending order of salary.
Exercise 9: Import a semicolon-separated CSV file.
Exercise 10: Export employee records using the \copy command.
Exercise 11: Import data into selected columns of an existing table.
Exercise 12: Export the result of a query containing WHERE, ORDER BY, and selected columns.
Exercise 13: Import a CSV file containing empty fields and examine how PostgreSQL handles NULL values.
Exercise 14: Identify and resolve a data type mismatch during CSV import.
Exercise 15: Compare importing the same CSV file using pgAdmin, COPY, and \copy.
COPY command?COPY and \copy?HEADER TRUE mean?DELIMITER option?| Command / Option | Purpose |
|---|---|
COPY table FROM |
Import data |
COPY table TO |
Export data |
\copy |
Client-side import/export |
FORMAT CSV |
Use CSV format |
FORMAT TEXT |
Use text format |
HEADER TRUE |
Handle column headings |
DELIMITER |
Specify field separator |
NULL |
Specify NULL representation |
WHERE |
Filter exported records |
ORDER BY |
Sort exported records |
| pgAdmin Import/Export | Graphical data transfer |
Next Lesson: PostgreSQL Backup and Restore
In the next lesson, students will learn how to create database backups, restore databases, and use PostgreSQL tools such as pg_dump and pg_restore.