Formating psql query output
Until I finish building the python/tkinter/psycopg2 front end to my business tracking tool I continue to work using the psql shell.' I have a working .sql script that reports my contacts between two dates; the script returns more columns than I want included in the report. I want to pipe the output through an awk script to extract, in order, the columns I need. I'm stuck at the point of defining options to psql. The current command line is: psql -d bustrac -f prospecting_log.sql -o contacts.txt --csv However, using the --csv output conversion makes separate fields from a varchar column that can contain commas with the text contents. Without --csv I get normal psql output with column headings and separator lines such as these: person_nbr | act_date | act_type | notes | person_nbr | lname | fname| org_nbr | org_nbr | org_name ++---+-++--++-+-+-- Is there an option that will retain the '|' separator but exclude the headings? Reading the psql document page I don't see such an option. TIA, Rich
RE: Formating psql query output
>From here: https://www.postgresql.org/docs/9.2/app-psql.html -F separator --field-separator=separator Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f. Bobb > -Original Message- > From: Rich Shepard > Sent: Monday, July 19, 2021 12:33 PM > To: pgsql-general@lists.postgresql.org > Subject: Formating psql query output > > Think Before You Click: This email originated outside our organization. > > > Until I finish building the python/tkinter/psycopg2 front end to my business > tracking tool I continue to work using the psql shell.' > > I have a working .sql script that reports my contacts between two dates; the > script returns more columns than I want included in the report. I want to pipe > the output through an awk script to extract, in order, the columns I need. I'm > stuck at the point of defining options to psql. > > The current command line is: > psql -d bustrac -f prospecting_log.sql -o contacts.txt --csv > > However, using the --csv output conversion makes separate fields from a > varchar column that can contain commas with the text contents. > > Without --csv I get normal psql output with column headings and separator > lines such as these: > person_nbr | act_date | act_type | > notes > | person_nbr | lname | fname| org_nbr | org_nbr | > org_name > ++---+-- > -- > -- > -- > -++--++-+-- > ---+-- > > Is there an option that will retain the '|' separator but exclude the > headings? > Reading the psql document page I don't see such an option. > > TIA, > > Rich > > >
Re: Formating psql query output
From: Rich Shepard Date: Monday, July 19, 2021 at 1:33 PM > Is there an option that will retain the '|' separator but exclude the > headings? > Reading the psql document page I don't see such an option. echo 'select 1,2,3,4;' | psql -At -F'|' 1|2|3|4 -A Switches to unaligned output mode. (The default output mode is aligned.) This is equivalent to \pset format unaligned. -t Turn off printing of column names and result row count footers, etc. This is equivalent to \t or \pset tuples_only. -F Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f. HTH
Re: Formating psql query output
On 7/19/21 10:33 AM, Rich Shepard wrote: Until I finish building the python/tkinter/psycopg2 front end to my business tracking tool I continue to work using the psql shell.' I have a working .sql script that reports my contacts between two dates; the script returns more columns than I want included in the report. I want to pipe the output through an awk script to extract, in order, the columns I need. I'm stuck at the point of defining options to psql. Is there a reason you can't just restrict the query to the columns you want? TIA, Rich -- Adrian Klaver adrian.kla...@aklaver.com
Re: Formating psql query output
> On Jul 19, 2021, at 11:49 AM, Adrian Klaver wrote: > > On 7/19/21 10:33 AM, Rich Shepard wrote: >> Until I finish building the python/tkinter/psycopg2 front end to my business >> tracking tool I continue to work using the psql shell.' >> I have a working .sql script that reports my contacts between two dates; the >> script returns more columns than I want included in the report. I want to >> pipe the output through an awk script to extract, in order, the columns I >> need. I'm stuck at the point of defining options to psql. > > Is there a reason you can't just restrict the query to the columns you want? > Also had that thought but OP is wise to head the other switches for cleaner output. But also should probably get familiar with to-json options as well for easy pickup on the eventual client.
RE: Formating psql query output
On Mon, 19 Jul 2021, Basques, Bob (CI-StPaul) wrote: -F separator --field-separator=separator Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f. Bobb, I should have mentioned that I tried that. Without the --csv option the results have the headings and separator. With --csv the field separator is ignored regardless of postion within the command string. Thanks, Rich
Re: Formating psql query output [RESOLVED]
On Mon, 19 Jul 2021, David Santamauro wrote: echo 'select 1,2,3,4;' | psql -At -F'|' 1|2|3|4 -A Switches to unaligned output mode. (The default output mode is aligned.) This is equivalent to \pset format unaligned. -t Turn off printing of column names and result row count footers, etc. This is equivalent to \t or \pset tuples_only. -F Use separator as the field separator for unaligned output. This is equivalent to \pset fieldsep or \f. David, HTH Sure enough, it does. And I learned more on using psql options. Thank you very much, Rich
Re: Formating psql query output
On Mon, 19 Jul 2021, Adrian Klaver wrote: Is there a reason you can't just restrict the query to the columns you want? Adrian, As far as I know I need to specify FK and PK columns when tables are joined; I don't need those key columns in the output. Thanks, Rich
Re: Formating psql query output
> On Jul 19, 2021, at 12:53 PM, Rich Shepard wrote: > > On Mon, 19 Jul 2021, Basques, Bob (CI-StPaul) wrote: > >> -F separator >> --field-separator=separator >> Use separator as the field separator for unaligned output. This is >> equivalent to \pset fieldsep or \f. > > Bobb, > > I should have mentioned that I tried that. Without the --csv option the > results > have the headings and separator. With --csv the field separator is ignored > regardless of postion within the command string. Can we see on line of the csv output? The field with commas should be in quotes, no? You’ll have write a “real” csv importer. awk =F”\”*,*\”” might, heavy on the might.
Re: Formating psql query output
On Mon, 19 Jul 2021, Rob Sargent wrote: Can we see on line of the csv output? The field with commas should be in quotes, no? You’ll have write a “real” csv importer. awk =F”\”*,*\”” might, heavy on the might. Rob, Here's a redacted output line: 8,2019-04-08,Phone,Went to voice mail @ 14:48; didn't leave a message. Call Wednesday morning,8,,,537,537, No quoted text fields. Rich
Re: Formating psql query output
> On Jul 19, 2021, at 1:07 PM, Rich Shepard wrote: > > On Mon, 19 Jul 2021, Rob Sargent wrote: > >> Can we see on line of the csv output? The field with commas should be in >> quotes, no? You’ll have write a “real” csv importer. awk =F”\”*,*\”” >> might, heavy on the might. > > Rob, > > Here's a redacted output line: > > 8,2019-04-08,Phone,Went to voice mail @ 14:48; didn't leave a message. Call > Wednesday morning,8,,,537,537, > > No quoted text fields. > > Rich > > Postgres version? >
Re: Formating psql query output
On 7/19/21 11:58 AM, Rich Shepard wrote: On Mon, 19 Jul 2021, Adrian Klaver wrote: Is there a reason you can't just restrict the query to the columns you want? Adrian, As far as I know I need to specify FK and PK columns when tables are joined; You need them in the JOIN and/or WHERE sections, but not necessarily in the field list in the SELECT portion. I don't need those key columns in the output. Thanks, Rich -- Adrian Klaver adrian.kla...@aklaver.com
Re: Formating psql query output
po 19. 7. 2021 v 21:07 odesílatel Rich Shepard napsal: > On Mon, 19 Jul 2021, Rob Sargent wrote: > > > Can we see on line of the csv output? The field with commas should be in > > quotes, no? You’ll have write a “real” csv importer. awk =F”\”*,*\”” > > might, heavy on the might. > > Rob, > > Here's a redacted output line: > > 8,2019-04-08,Phone,Went to voice mail @ 14:48; didn't leave a message. Call > Wednesday morning,8,,,537,537, > > No quoted text fields. > text fields are quoted only when it is necessary [pavel@localhost src]$ psql -c "select 'ahoj' as x, 'svete' as y" --csv Assertions: on x,y ahoj,svete [pavel@localhost src]$ psql -c "select 'ahoj' as x, 'sve,te' as y" --csv Assertions: on x,y ahoj,"sve,te" If you need forced quoting, you need to use COPY TO STDOUT statement Regards Pavel > > Rich > > > >
Re: Formating psql query output
po 19. 7. 2021 v 21:12 odesílatel Pavel Stehule napsal: > > > po 19. 7. 2021 v 21:07 odesílatel Rich Shepard > napsal: > >> On Mon, 19 Jul 2021, Rob Sargent wrote: >> >> > Can we see on line of the csv output? The field with commas should be in >> > quotes, no? You’ll have write a “real” csv importer. awk =F”\”*,*\”” >> > might, heavy on the might. >> >> Rob, >> >> Here's a redacted output line: >> >> 8,2019-04-08,Phone,Went to voice mail @ 14:48; didn't leave a message. >> Call >> Wednesday morning,8,,,537,537, >> >> No quoted text fields. >> > > text fields are quoted only when it is necessary > > [pavel@localhost src]$ psql -c "select 'ahoj' as x, 'svete' as y" --csv > Assertions: on > x,y > ahoj,svete > [pavel@localhost src]$ psql -c "select 'ahoj' as x, 'sve,te' as y" --csv > Assertions: on > x,y > ahoj,"sve,te" > > If you need forced quoting, you need to use COPY TO STDOUT statement > [pavel@localhost src]$ psql -c "copy (select 'ahoj' as x, 'svete' as y) to stdout force quote * csv header" Assertions: on x,y "ahoj","svete" > > Regards > > Pavel > >> >> Rich >> >> >> >>
Re: Formating psql query output
On Mon, 19 Jul 2021, Rob Sargent wrote: Postgres version? postgresql-12.7-x86_64-1_SBo
Re: Formating psql query output
On Mon, 19 Jul 2021, Adrian Klaver wrote: You need them in the JOIN and/or WHERE sections, but not necessarily in the field list in the SELECT portion. Adrian, I wondered about that and thought I needed to include them in the SELECT phrase. Thanks for the lesson. Regards, Rich
Re: Formating psql query output
On 7/19/21 3:05 PM, Rich Shepard wrote: On Mon, 19 Jul 2021, Adrian Klaver wrote: You need them in the JOIN and/or WHERE sections, but not necessarily in the field list in the SELECT portion. Adrian, I wondered about that and thought I needed to include them in the SELECT phrase. If that were the case, then there would be no purpose in enumerating columns instead of writing "SELECT T1.*, T2.*" -- Angular momentum makes the world go 'round.
