Обсуждение: What is field separator?

Tena Sakai ha scritto:
> Can somebody please tell me what “|” is called in my example above?
> And is it possible to change it from “|” to something else for
> display purpose?
The field separatore in psql is used only when the format is "unaligned"
(which does not happen to be default).

In order for it to be effective, you need to type in psql:

\pset format unaligned
\pset fieldsep '\t'

This will give you an output with columns that are separated by tab.

However, I suggest that you look at the documentation for more options
and ways to achive the same results:
http://www.postgresql.org/docs/current/static/app-psql.html

Cheers,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

Thank you, Gabriele.

Now I realize "context sensitive" nature of field separator.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 5/30/10 1:37 AM, "Gabriele Bartolini" <gabriele.bartolini@2ndQuadrant.it>
wrote:

> Tena Sakai ha scritto:
>> Can somebody please tell me what ³|² is called in my example above?
>> And is it possible to change it from ³|² to something else for
>> display purpose?
> The field separatore in psql is used only when the format is "unaligned"
> (which does not happen to be default).
>
> In order for it to be effective, you need to type in psql:
>
> \pset format unaligned
> \pset fieldsep '\t'
>
> This will give you an output with columns that are separated by tab.
>
> However, I suggest that you look at the documentation for more options
> and ways to achive the same results:
> http://www.postgresql.org/docs/current/static/app-psql.html
>
> Cheers,
> Gabriele

Tena Sakai wrote:
>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>  > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
> What's denoted as \t is not a tab character.  It is a two character
> sequence backslash followd by a t.  I have tried all other possibilities
> such as \\t , "\t", etc that I can think of, but I cannot get an honest to
> god tab character to appear as field separator.

Yeah, that's a tough one.  In bash you can use ANSI C quoting to pull
this off:

psql -c "select name,setting from pg_settings limit 1" -d postgres -At
-F $'\t'

See http://wiki.bash-hackers.org/syntax/quoting for more information.
Portability to other shells I'm not sure about.

Note that I did two other things a little differently than your example,
as well as tightening up the abbreviations:

-Putting the command in -c " " is just better all around than piping
into psql using echo.  It even works with multi-line input, i.e.:

psql -c "
select 1
"

-Explicitly specifying the database manually using -d makes what's
happening easier to follow than expecting people to know the convention
that the first non-option passed to psql is a database name.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Excerpts from Tena Sakai's message of lun may 31 21:36:41 -0400 2010:

> But when I do this:
>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>  > psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
> (here the only change from the previous is the specification of fieldsep)
> Then I get:
>   RS3094315\t0.578121\t0.735

Use a literal tab.  You can enter this in decent shells with Ctrl-V followed by a tab.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Many thanks, Greg!

I got it to work by emulating what you wrote.
But I think you meant -A, not -At below.
>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>> -F $'\t'

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 5/31/10 8:07 PM, "Greg Smith" <greg@2ndquadrant.com> wrote:

> Tena Sakai wrote:
>>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>>> psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
>> What's denoted as \t is not a tab character.  It is a two character
>> sequence backslash followd by a t.  I have tried all other possibilities
>> such as \\t , "\t", etc that I can think of, but I cannot get an honest to
>> god tab character to appear as field separator.
>
> Yeah, that's a tough one.  In bash you can use ANSI C quoting to pull
> this off:
>
> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
> -F $'\t'
>
> See http://wiki.bash-hackers.org/syntax/quoting for more information.
> Portability to other shells I'm not sure about.
>
> Note that I did two other things a little differently than your example,
> as well as tightening up the abbreviations:
>
> -Putting the command in -c " " is just better all around than piping
> into psql using echo.  It even works with multi-line input, i.e.:
>
> psql -c "
> select 1
> "
>
> -Explicitly specifying the database manually using -d makes what's
> happening easier to follow than expecting people to know the convention
> that the first non-option passed to psql is a database name.

Thank you, Alvaro.

Literal tab, that's one thing I never tried.
Yes, it works.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 5/31/10 8:58 PM, "Alvaro Herrera" <alvherre@commandprompt.com> wrote:

> Excerpts from Tena Sakai's message of lun may 31 21:36:41 -0400 2010:
>
>> But when I do this:
>>   $ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
>>> psql -P 'format=unaligned' -P 'fieldsep=\t' -t -f - musket
>> (here the only change from the previous is the specification of fieldsep)
>> Then I get:
>>   RS3094315\t0.578121\t0.735
>
> Use a literal tab.  You can enter this in decent shells with Ctrl-V followed
> by a tab.

Tena Sakai wrote:
> I got it to work by emulating what you wrote.
> But I think you meant -A, not -At below.
>
>>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>>> -F $'\t'
>>>

You had -t in your original to turn off the display of column names and
the counts at the end, and -A is the shortcut for what you had as "-P
'format=unaligned'".  I throw "-At" into almost every use of psql from a
bash script I do, that's the usual combination that gets the basic
format to be right; then tweak things like the field separator afterwards.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Hi Greg,

My apologies.  I hadn't realized that -At was a combination
of -A and -t.  Rather, I kept thinking that t as argument
to -A.

Regards,

Tena Sakai
tsakai@gallo.ucsf.edu


On 6/1/10 11:02 PM, "Greg Smith" <greg@2ndquadrant.com> wrote:

> Tena Sakai wrote:
>> I got it to work by emulating what you wrote.
>> But I think you meant -A, not -At below.
>>
>>>> psql -c "select name,setting from pg_settings limit 1" -d postgres -At
>>>> -F $'\t'
>>>>
>
> You had -t in your original to turn off the display of column names and
> the counts at the end, and -A is the shortcut for what you had as "-P
> 'format=unaligned'".  I throw "-At" into almost every use of psql from a
> bash script I do, that's the usual combination that gets the basic
> format to be right; then tweak things like the field separator afterwards.