Thread: 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

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 <rshepard@appl-ecosys.com>
> 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
>
>
>

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.klaver@aklaver.com

> On Jul 19, 2021, at 11:49 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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
familiarwith to-json options as well for easy pickup on the eventual client. 

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

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

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

> On Jul 19, 2021, at 12:53 PM, Rich Shepard <rshepard@appl-ecosys.com> 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. 

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,<lname>,<fname>,537,537,<company_name>

No quoted text fields.

Rich

> On Jul 19, 2021, at 1:07 PM, Rich Shepard <rshepard@appl-ecosys.com> 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,<lname>,<fname>,537,537,<company_name>
>
> No quoted text fields.
>
> Rich
>
>
Postgres version?
>

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.klaver@aklaver.com

On Mon, 19 Jul 2021, Rob Sargent wrote:

> Postgres version?

postgresql-12.7-x86_64-1_SBo

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

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.