pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat is field separator?
10+ messages / 4 participants
[nested] [flat]
* What is field separator?
@ 2010-05-30 04:19 Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 10+ messages in thread
From: Tena Sakai @ 2010-05-30 04:19 UTC (permalink / raw)
To: pgsql-admin
Hi Everybody,
I am a bit confused about field separator.
In psql, \pset command lets me change this value from the default
to something else. I thought field separator was “|” below:
marker | allele | afreq | fam_ | p
------------+--------+-------+------+----------
RS2312724 | 2 | 0.125 | 25 | 0.271484
RS1020382 | 2 | 0.417 | 69 | 0.609682
RS11878315 | 2 | 0.335 | 64 | 0.354771
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?
Thank you.
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-05-30 08:37 Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 2 replies; 10+ messages in thread
From: Gabriele Bartolini @ 2010-05-30 08:37 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-05-31 03:25 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
1 sibling, 0 replies; 10+ messages in thread
From: Tena Sakai @ 2010-05-31 03:25 UTC (permalink / raw)
To: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-06-01 01:36 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
1 sibling, 2 replies; 10+ messages in thread
From: Tena Sakai @ 2010-06-01 01:36 UTC (permalink / raw)
To: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>; +Cc: pgsql-admin
Hi Gabriele,
This isn’t strictly a psql problem, but please humor me.
I can do below from linux prompt:
$ echo "select marker, p, afreq from gallo.fbat_sdsdecode limit 10" | \
> psql -P 'format=unaligned' -P 'fieldsep=,' -t -f - musket
And get:
RS3094315,0.578121,0.735
RS3094315,0.578121,0.265
RS3115850,0.365978,0.251
RS3115850,0.365978,0.749
RS12562034,,0.135
RS12562034,,0.865
RS4475691,0.247133,0.710
RS4475691,0.247133,0.290
RS28705211,,0.725
RS28705211,,0.275
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
RS3094315\t0.578121\t0.265
RS3115850\t0.365978\t0.251
RS3115850\t0.365978\t0.749
RS12562034\t\t0.135
RS12562034\t\t0.865
RS4475691\t0.247133\t0.710
RS4475691\t0.247133\t0.290
RS28705211\t\t0.725
RS28705211\t\t0.275
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. I can do it from psql
and achieve what I want, but I need to do this from shell level. Any
suggestion what else I can try?
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-06-01 03:07 Greg Smith <greg@2ndquadrant.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
1 sibling, 1 reply; 10+ messages in thread
From: Greg Smith @ 2010-06-01 03:07 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-06-01 03:58 Alvaro Herrera <alvherre@commandprompt.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
1 sibling, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2010-06-01 03:58 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: Gabriele Bartolini <gabriele.bartolini@2ndquadrant.it>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-06-01 05:27 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tena Sakai @ 2010-06-01 05:27 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-06-01 05:29 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tena Sakai @ 2010-06-01 05:29 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Gabriele Bartolini <gabriele.bartolini@2ndquadrant.it>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-06-02 06:02 Greg Smith <greg@2ndquadrant.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 10+ messages in thread
From: Greg Smith @ 2010-06-02 06:02 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: What is field separator?
@ 2010-06-02 06:05 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tena Sakai @ 2010-06-02 06:05 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2010-06-02 06:05 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-30 04:19 What is field separator? Tena Sakai <tsakai@gallo.ucsf.edu>
2010-05-30 08:37 ` Gabriele Bartolini <gabriele.bartolini@2ndQuadrant.it>
2010-05-31 03:25 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2010-06-01 01:36 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2010-06-01 03:07 ` Greg Smith <greg@2ndquadrant.com>
2010-06-01 05:27 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2010-06-02 06:02 ` Greg Smith <greg@2ndquadrant.com>
2010-06-02 06:05 ` Tena Sakai <tsakai@gallo.ucsf.edu>
2010-06-01 03:58 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-06-01 05:29 ` Tena Sakai <tsakai@gallo.ucsf.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox