CODEWITHSUNDEEP
phpdatabasepsql
code-8
code-8

Reputation: 58810

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/site/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/site/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..."

How do I fix this? Is it something with my export command?

Upvotes: 0

Views: 62

Answers (1)

Kevin Stich
Kevin Stich

Reputation: 783

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

Upvotes: 1

Related Questions