question about \encoding option of psql

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