ihue ihue - 9 days ago 5
MySQL Question

Export a 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.

I've tried



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 my
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





My goal is get my
.csv
to exactly in this format

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


Because it is the only format that will accept by
psql
.




Question



How do I adjust my psql command to get that ?

OR

Updating it using PHP is my only option ?




I'm opening to any suggestions at this moment.

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

Answer

Looks like the FORCE_QUOTE option on COPY can take a list of columns to wrap. See here: SQL COPY

FORCE_QUOTE { ( column_name [, ...] ) | * }

This means you can specify or it does all by default, you probably want the specify given your id column is not quote wrapped in your example.