Subject: psql: print values and return the COUNT(*) value to bash?

From: Ron Johnson <[email-masked]>
Date: Mon, 29 Dec 2025 02:21:41 -0500

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!

---

From: Jehan-Guillaume de Rorthais <[email-masked]>
Date: Mon, 29 Dec 2025 09:12:36 +0100

On Mon, 29 Dec 2025 02:21:41 -0500
Ron Johnson <[email-masked]> wrote:
…
> also test the COUNT(*) value and bypass other bash commands if the count ==

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

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.

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 29 Dec 2025 11:11:58 +0100

>  days_ago | oldest_date | cnt

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