pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedpsql: print values and return the COUNT(*) value to bash?
5+ messages / 4 participants
[nested] [flat]
* psql: print values and return the COUNT(*) value to bash?
@ 2025-12-29 07:21 Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ron Johnson @ 2025-12-29 07:21 UTC (permalink / raw)
To: pgsql-general
Currently I do this in a bash script:
Tbl=table_1
Fld=field_1
Sql="SELECT CURRENT_TIMESTAMP - MIN($Fld) AS days_ago
, MIN($Fld) as oldest_date
, COUNT(*) AS cnt
FROM $Tbl;"
psql -h <host> <db> -Xc "${Sql}"
days_ago | oldest_date | cnt
----------+-------------+-----
| | 0
(1 row)
More stuff happens after this. I want to print out these four lines, but
also test the COUNT(*) value and bypass other bash commands if the count ==
0.
Is there any relatively simple way to do this (psql print and return
count(*))?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: psql: print values and return the COUNT(*) value to bash?
@ 2025-12-29 08:12 Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jehan-Guillaume de Rorthais @ 2025-12-29 08:12 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general
On Mon, 29 Dec 2025 02:21:41 -0500
Ron Johnson <ronljohnsonjr@gmail.com> wrote:
…
> More stuff happens after this. I want to print out these four lines, but
> also test the COUNT(*) value and bypass other bash commands if the count ==
> 0.
You should probably output your result from psql in a parsable way (CSV ? TSV ?
custom ?) for your bash script, then print values from bash. The commands
"column" or "printf" could help you format a pretty table if really needed.
> Is there any relatively simple way to do this (psql print and return
> count(*))?
Not in a clean way. To make psql return something else than 0, you need to
trigger an error with a failing query to make it return 3 when ON_ERROR_STOP is
enabled.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: psql: print values and return the COUNT(*) value to bash?
@ 2025-12-29 10:11 hubert depesz lubaczewski <depesz@depesz.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2025-12-29 10:11 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general
On Mon, Dec 29, 2025 at 02:21:41AM -0500, Ron Johnson wrote:
> Currently I do this in a bash script:
> Tbl=table_1
> Fld=field_1
> Sql="SELECT CURRENT_TIMESTAMP - MIN($Fld) AS days_ago
> , MIN($Fld) as oldest_date
> , COUNT(*) AS cnt
> FROM $Tbl;"
> psql -h <host> <db> -Xc "${Sql}"
> days_ago | oldest_date | cnt
> ----------+-------------+-----
> | | 0
> (1 row)
>
> More stuff happens after this. I want to print out these four lines, but
> also test the COUNT(*) value and bypass other bash commands if the count ==
> 0.
>
> Is there any relatively simple way to do this (psql print and return
> count(*))?
1. Don't interpolate $variables to your sql. Instead use -v "t=$Tbl" -v
"f=$Fld" options to psql, and then make your query use :"t" and :"f"
- it's safer.
2. Catch output of the psql to variable, and check last line, there is
rowcount there.
Example:
=$ cat z.sh
#!/usr/bin/env bash
table=pg_stat_all_tables
field=last_autovacuum
returned="$( psql -d depesz_explain -v "t=$table" -v "f=$field" -X << _SQL_
select current_timestamp - min(:"f") as days_ago
, min(:"f") as oldest_date
, count(*) as cnt
from :"t"
_SQL_
)"
row_count="$( tail -n1 <<< "${returned}" | tr -cd 0-9 )"
echo "Rowcount = $row_count"
echo "Full output:"
echo "${returned}"
# vim: set filetype=bash shiftwidth=4 expandtab smarttab softtabstop=4 tabstop=4 textwidth=132 :
=$ bash z.sh
Rowcount = 1
Full output:
days_ago | oldest_date | cnt
------------------------+-------------------------------+-----
7 days 03:48:07.348247 | 2025-12-22 07:22:32.593814+01 | 212
(1 row)
Best regards,
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: psql: print values and return the COUNT(*) value to bash?
@ 2025-12-29 16:40 Vincent Veyron <vv.lists@wanadoo.fr>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 5+ messages in thread
From: Vincent Veyron @ 2025-12-29 16:40 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general
On Mon, 29 Dec 2025 11:11:58 +0100
hubert depesz lubaczewski <depesz@depesz.com> wrote:
Hi Ron, Hubert,
>
> Example:
>
> =$ cat z.sh
> #!/usr/bin/env bash
>
> table=pg_stat_all_tables
> field=last_autovacuum
>
> returned="$( psql -d depesz_explain -v "t=$table" -v "f=$field" -X << _SQL_
> select current_timestamp - min(:"f") as days_ago
> , min(:"f") as oldest_date
> , count(*) as cnt
> from :"t"
> _SQL_
> )"
> row_count="$( tail -n1 <<< "${returned}" | tr -cd 0-9 )"
> echo "Rowcount = $row_count"
> echo "Full output:"
> echo "${returned}"
>
> # vim: set filetype=bash shiftwidth=4 expandtab smarttab softtabstop=4 tabstop=4 textwidth=132 :
>
> =$ bash z.sh
> Rowcount = 1
> Full output:
> days_ago | oldest_date | cnt
> ------------------------+-------------------------------+-----
> 7 days 03:48:07.348247 | 2025-12-22 07:22:32.593814+01 | 212
> (1 row)
>
IIUC, Ron wants the 212 number?
Maybe use the -t switch, and 'cut' :
#!/usr/bin/env bash
table=pg_stat_all_tables
field=last_autovacuum
returned="$( psql -t -d vv -v "t=$table" -v "f=$field" -X << _SQL_
select current_timestamp - min(:"f") as days_ago
, min(:"f") as oldest_date
, count(*) as cnt
from :"t"
_SQL_
)"
row_count="$( tail -n1 <<< "${returned}" | cut -d "|" -f 3)"
echo "Rowcount = $row_count"
echo "Full output:"
echo "${returned}"
--
Bien à vous, Vincent Veyron
https://compta.libremen.com
Logiciel libre de comptabilité générale et analytique en partie double
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: psql: print values and return the COUNT(*) value to bash?
@ 2025-12-30 05:26 hubert depesz lubaczewski <depesz@depesz.com>
parent: Vincent Veyron <vv.lists@wanadoo.fr>
0 siblings, 0 replies; 5+ messages in thread
From: hubert depesz lubaczewski @ 2025-12-30 05:26 UTC (permalink / raw)
To: Vincent Veyron <vv.lists@wanadoo.fr>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general
On Mon, Dec 29, 2025 at 05:40:23PM +0100, Vincent Veyron wrote:
> > =$ bash z.sh
> > Rowcount = 1
> > Full output:
> > days_ago | oldest_date | cnt
> > ------------------------+-------------------------------+-----
> > 7 days 03:48:07.348247 | 2025-12-22 07:22:32.593814+01 | 212
> > (1 row)
> >
>
> IIUC, Ron wants the 212 number?
>
> Maybe use the -t switch, and 'cut' :
Ah, that part I misunderstood.
Well, if there is only 1 row returned, always, then the simplest thing
would be to change
> row_count="$( tail -n1 <<< "${returned}" | cut -d "|" -f 3)"
to this:
row_count="$( awk 'NR==3 {print $NF}' <<< "${returned}" )"
format can stay the same, we don't need to psql -t, or -a, and it will
just work :)
Best regards,
depesz
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-12-30 05:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-29 07:21 psql: print values and return the COUNT(*) value to bash? Ron Johnson <ronljohnsonjr@gmail.com>
2025-12-29 08:12 ` Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
2025-12-29 10:11 ` hubert depesz lubaczewski <depesz@depesz.com>
2025-12-29 16:40 ` Vincent Veyron <vv.lists@wanadoo.fr>
2025-12-30 05:26 ` hubert depesz lubaczewski <depesz@depesz.com>
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