How to Export and Import PostgreSQL tables using CSV

Feb 21, 2022 · 1 min read · Post a comment
How to Export and Import PostgreSQL tables using CSV

Export and Import operations in PostgreSQL and any other relation database management system for that matter, could include a couple of use-case scenarios, for instance data analysis. Since CSV (comma-separated values) files are the most common ones, let’s see how can we do both export and import.


  • PostgreSQL

Export table

psql -h <host> -p <port> -U <user> -c "\COPY <table_name> TO STDOUT (DELIMITER ',')" > <table_name>.csv


psql -h <host> -p <port> -U <user> -c "\COPY (SELECT * FROM <table_name>) TO STDOUT (DELIMITER ',')" > <table_name>.csv


  • Delimiter represents a character that separates columns within each row of the file.
  • Both examples above will export the table with all of the columns. If you want to be more flexible with which column get exported and which not, use something like
    psql -h <host> -p <port> -U <user> -c "\COPY (SELECT <column_1>, <column_2> FROM <table_name>) TO STDOUT (DELIMITER ',')" > <table_name>.csv

Import table

psql -h <host> -p <port> -U <user> -c "\COPY <table_name> FROM '/dir_path/table_name.csv' (DELIMITER ',');"


psql -h <host> -p <port> -U <user> -c "\COPY <table_name> FROM '/dir_path/table_name.csv' WITH DELIMITER ',' CSV;"


  • By default, the header row will be exported as well containing the column names. If you want to skip the header, just add HEADER after CSV. For instance:
    psql -h <host> -p <port> -U <user> -c "\COPY <table_name> FROM '/dir_path/table_name.csv' WITH DELIMITER ',' CSV HEADER;"


Check the official PostgreSQL documentation for more info.
On a side note, follow our official channel on Telegram.