Thread: question about \encoding option of psql

question about \encoding option of psql

From
Xiaobo Gu
Date:
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

Re: question about \encoding option of psql

From
John R Pierce
Date:
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


Re: question about \encoding option of psql

From
Guillaume Lelarge
Date:
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


Re: question about \encoding option of psql

From
Adrian Klaver
Date:
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

Re: question about \encoding option of psql

From
Xiaobo Gu
Date:
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

Re: question about \encoding option of psql

From
Adrian Klaver
Date:
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