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 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 ',');"

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 the official PostgreSQL documentation for more info.
On a side note, follow our official channel on Telegram.