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

From: Rich Shepard <rshepard@appl-ecosys.com>
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

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 familiar with 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

po 19. 7. 2021 v 21:07 odesílatel Rich Shepard <rshepard@appl-ecosys.com>
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,<lname>,<fname>,537,537,<company_name>

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

po 19. 7. 2021 v 21:12 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

po 19. 7. 2021 v 21:07 odesílatel Rich Shepard <rshepard@appl-ecosys.com>
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,<lname>,<fname>,537,537,<company_name>

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"

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.