Обсуждение: question about \encoding option of psql
Hi, I know \encoding is a meta command to set client encoding on psql prompt, but how can I set it inside the psql command line which will be called inside shell scripts, psql -h 192.168.72.7 -U gpadmin -w -d miner_demo -c"\copy demo.store to 'd:\store.csv' with csv header" How can I set the encoding for the export csv file? Regards, Xiaobo Gu
On 12/14/11 10:12 PM, Xiaobo Gu wrote: > I know \encoding is a meta command to set client encoding on psql > prompt, but how can I set it inside the psql command line which will > be called inside shell scripts, > > psql -h 192.168.72.7 -U gpadmin -w -d miner_demo -c"\copy demo.store > to 'd:\store.csv' with csv header" > > > How can I set the encoding for the export csv file? one thing that comes to mind, put the \encoding and \copy commands in a .sql file, and invoke it with psql .... -f file.sql -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Wed, 2011-12-14 at 22:26 -0800, John R Pierce wrote: > On 12/14/11 10:12 PM, Xiaobo Gu wrote: > > I know \encoding is a meta command to set client encoding on psql > > prompt, but how can I set it inside the psql command line which will > > be called inside shell scripts, > > > > psql -h 192.168.72.7 -U gpadmin -w -d miner_demo -c"\copy demo.store > > to 'd:\store.csv' with csv header" > > > > > > How can I set the encoding for the export csv file? > > one thing that comes to mind, put the \encoding and \copy commands in a > .sql file, and invoke it with psql .... -f file.sql > Using the PGCLIENTENCODING environment variable might be a better option. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Wednesday, December 14, 2011 10:12:40 pm Xiaobo Gu wrote: > Hi, > > I know \encoding is a meta command to set client encoding on psql > prompt, but how can I set it inside the psql command line which will > be called inside shell scripts, > > psql -h 192.168.72.7 -U gpadmin -w -d miner_demo -c"\copy demo.store > to 'd:\store.csv' with csv header" > > > How can I set the encoding for the export csv file? http://www.postgresql.org/docs/9.0/interactive/app-psql.html -c command --command command Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc) are ignored with this option. command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. Thus you cannot mix SQL and psql meta-commands with this option. To achieve that, you could pipe the string into psql, like this: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator meta-command.) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to psql's standard input. > > Regards, > > Xiaobo Gu -- Adrian Klaver adrian.klaver@gmail.com
Hi Adrian, I am on the Windows platform, how should I paste the command line. Regards, Xiaobo Gu On Thu, Dec 15, 2011 at 10:44 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Wednesday, December 14, 2011 10:12:40 pm Xiaobo Gu wrote: >> Hi, >> >> I know \encoding is a meta command to set client encoding on psql >> prompt, but how can I set it inside the psql command line which will >> be called inside shell scripts, >> >> psql -h 192.168.72.7 -U gpadmin -w -d miner_demo -c"\copy demo.store >> to 'd:\store.csv' with csv header" >> >> >> How can I set the encoding for the export csv file? > > > > http://www.postgresql.org/docs/9.0/interactive/app-psql.html > -c command > --command command > > Specifies that psql is to execute one command string, command, and then exit. > This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc) are > ignored with this option. > > command must be either a command string that is completely parsable by the > server (i.e., it contains no psql-specific features), or a single backslash > command. Thus you cannot mix SQL and psql meta-commands with this option. To > achieve that, you could pipe the string into psql, like this: echo '\x \\ SELECT > * FROM foo;' | psql. (\\ is the separator meta-command.) > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > > > If the command string contains multiple SQL commands, they are processed in > a single transaction, unless there are explicit BEGIN/COMMIT commands included > in the string to divide it into multiple transactions. This is different from the > behavior when the same string is fed to psql's standard input. > > >> >> Regards, >> >> Xiaobo Gu > > -- > Adrian Klaver > adrian.klaver@gmail.com
On Thursday, December 15, 2011 10:33:03 pm Xiaobo Gu wrote: > Hi Adrian, > > I am on the Windows platform, how should I paste the command line. I don't use Windows enough to be sure but: http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/echo.mspx?mfr=true So open a command prompt and give it a try. I would test with a non destructive command first:) For your original request something like: echo "\your_encoding \\ \copy demo.store to 'd:\store.csv' with csv header"| psql -h 192.168.72.7 -U gpadmin -w -d miner_demo > > Regards, > > Xiaobo Gu -- Adrian Klaver adrian.klaver@gmail.com