Thread: What is field separator?
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
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
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
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
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
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.