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
afterCSV
. 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.