ihue ihue - 3 days ago 4
PHP Question

Export + Import PSQL Table in PHP

I have a languages tables in psql that look like this.

enter image description here




My goal is to export my languages table and import that back in using PHP.

PHP

Export

shell_exec('psql -E -U postgres -d portal -c "COPY languages TO \'/Applications/MAMP/htdocs/code/benu/ssc-portal/public/csv/languages.csv\' DELIMITER \',\' "');


I got
languages.csv
to export successfully.

If I open it up, it contain this.

1,h,Advanced Settings,Geavanceerde instellingen,Ajustes avanzados,Réglages avancés,2016-11-23 14:41:25,2016-11-23 14:41:25





Import

Now, I'm moving on to my import, here is what I did

shell_exec('psql -E -U postgres -d portal -c "COPY languages (code, text, nl, es, fr, created_at, updated_at) FROM \'/Applications/MAMP/htdocs/code/benu/ssc-portal/public/csv/languages.csv\' DELIMITER \',\' csv;"');


I kept getting

ERROR: invalid input syntax for integer: "1,h,Advanced Settings,Geavanceerde instellingen,Ajustes avanzados,Réglages avancés,2016-11-23 14:41:25,2016-11-23 14:41:25"


CONTEXT: COPY languages, line 1, column id: "1,h,Advanced Settings,Geavanceerde instellingen,Ajustes avanzados,Réglages avancés,2016-11-23 14:4..."




Question



How do I fix this ?

Is it something with my export command ?

How would one go about and debug this further ?




I'm opening to any suggestions at this moment.

Any hints / suggestions / helps on this be will be much appreciated !

Answer

Your COPY command for import has one less column than the CSV file. It's trying to grab your updated_at column as the second row's code field.

Table def:

(code, text, nl, es, fr, created_at, updated_at)

Row:

"1,h,Advanced Settings,Geavanceerde instellingen,Ajustes avanzados,Réglages avancés,2016-11-23 14:41:25,2016-11-23 14:41:25"

Map:

code - 1
text - "h"
nl - "Advanced Settings"
es - "Geavanceerde instellingen"
fr - "Ajustes avanzados"
created_at - "Réglages avancés"
updated_at - 2016-11-23 14:41:25
code2 - 2016-11-23 14:41:25 // <-- Problem
Comments