PostgreSQL: Data Copy & Import using CSV files

PostgreSQL is a powerful open-source database management system offering flexibility and ease of use with many advanced features.

This post explains how we can use CSV files for data copy operations in PostgreSQL.

Copy data from one or more postgreSQL tables to another table

The below query copies data returned from a join query into a CSV file with comma as a delimiter.

If any of this data contains text with commas, then it will not get correctly stored in CSV. In that case, we can use other characters like pipe operator | as a delimiter.

Copy (
select schedules.*
from users,
user_locations,
locations,
schedules
where user_locations.user_id = users.id
and user_locations.location_id = locations.id
and schedules.location_id = locations.id
and users.active = 'true'
)
To '/home/data/schedules.csv'
With CSV HEADER DELIMITER ',';

The CSV file created here will have column headers. Hence, the HEADER keyword in queries.

Below query is to import this data to a new table which may or may not be in the same database. In case the new database is on a different server, then this CSV file first needs to be copied to the other server.

Copy active_schedules
From ‘/home/data/schedules.csv’
CSV HEADER DELIMITER ‘,’;

If the table has id as an identity column, we have to reset the identity to the maximum id count in the table.

SELECT setval(‘active_schedules_id_seq’, (select max(id) from active_schedules));

Import data from CSV to PostgreSQL table

Suppose a table user_names has 4 columns. For importing data, we need a CSV file with data populated as per table structure.

/home/data/user_names.csv

Save this file on the server where postgreSQL is installed and run the below command to import data from CSV to table:

Copy user_names(id,name,created_at,updated_at)
From ‘/home/data/user_names.csv’
CSV HEADER DELIMITER ‘,’;

A data import for around 100,000 records finishes under 1 second using PostgreSQL 9.5 !

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.