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.

Prerequisites

• PostgreSQL

Export table

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

or

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

Note(s):

• Delimiter represents a character that separates columns within each row of the file.
• Both examples above will export the table with all 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 ',');"

or

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

Note(s):

• 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;"
  

Conclusion

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