PostgreSQL CSV Import and Export Guide

Importing Data from a CSV File

Prerequisites

• Ensure the PostgreSQL table is already created
• CSV file should match the table schema
• The \COPY command is run from the psql command-line interface

Basic Import Syntax

\COPY table_name FROM '/path/to/file.csv' 
DELIMITER ',' 
CSV HEADER;

Example Scenario

Sample CSV File (students.csv)

id,name,age,grade
1,John Doe,20,A
2,Jane Smith,22,B
3,Mike Brown,21,A-

Import Command

\COPY students FROM '/path/to/students.csv' 
DELIMITER ',' 
CSV HEADER;

Exporting Data to a CSV File

Basic Export Syntax

\COPY table_name TO '/path/to/exported_file.csv' 
DELIMITER ',' 
CSV HEADER;

Example Scenario

Export Command

\COPY students TO '/path/to/exported_students.csv' 
DELIMITER ',' 
CSV HEADER;

Important Notes

• The HEADER option includes column names in the first row
• Use ',' as the delimiter for standard CSV files
• Ensure the PostgreSQL user has file system access to the specified paths
• For large files, consider using COPY instead of \COPY (requires superuser privileges)

Common Options

• CSV: Specifies CSV format
• DELIMITER ',': Sets the field separator
• HEADER: Indicates the first row contains column names