Thread
-
psql: print values and return the COUNT(*) value to bash?
Ron Johnson <ronljohnsonjr@gmail.com> — 2025-12-29T07:21:41Z
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! -
Re: psql: print values and return the COUNT(*) value to bash?
Jehan-Guillaume de Rorthais <jgdr@dalibo.com> — 2025-12-29T08:12:36Z
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.
-
Re: psql: print values and return the COUNT(*) value to bash?
hubert depesz lubaczewski <depesz@depesz.com> — 2025-12-29T10:11:58Z
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 -
Re: psql: print values and return the COUNT(*) value to bash?
Vincent Veyron <vv.lists@wanadoo.fr> — 2025-12-29T16:40:23Z
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 -
Re: psql: print values and return the COUNT(*) value to bash?
hubert depesz lubaczewski <depesz@depesz.com> — 2025-12-30T05:26:53Z
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