Differences

This shows you the differences between two versions of the page.

--- linux:database:postgresql:import_with_copy [2019/06/14 12:38]
jlucas
+++ linux:database:postgresql:import_with_copy [2019/06/14 12:46] (current)
jlucas
@@ Line 1: / Line 1: @@
-By default, when you export data from postgres, it does not use a standard CSV format.
+Using PostgreSQL.  To export table data, perform the following command:
-  psql -U system_user -c "SELECT * FROM table_name" > exported_data.csv
+  sudo -u system_user psql -d database -c "\copy (SELECT * FROM table_name) to '/tmp/output_data.csv' (format csv, delimiter ',') "
-To correct for the default format of the export, perform this on the exported data:
+To import the data, use this command:
-  tail exported_data.csv | sed -e "s/ *|/|/g" -e "s/| */|/g" -e 's/^ *//' > input_file.csv
+  sudo -u system_user psql -d database -c "\copy table_name FROM '/tmp/import_data.csv' (format csv, delimiter ',')"
-Then, you can import the data like this:
-  sudo -u system_user psql -d database -c "\copy table_name (specify columns) FROM 'input_file.csv'  (DELIMITER '|', FORMAT CSV, NULL '\N')"
-  sudo -u system_user psql -c "\copy table_name FROM STDIN (format csv, delimiter ',') " < import_data.csv

Trace: