0

I'd like to pipe live data from psql into another command for purposes of monitoring via the shell.

Can this be done?

So far I have the following:

$ psql bareos -c '\watch (SELECT * FROM log ORDER BY time DESC LIMIT 200) TO STDOUT WITH CSV' 

I'm using postgres 11.12.

But when I run the command, I end up with the error:

\watch cannot be used with an empty query

Whereas, if I run it directly from psql, it's perfectly capable of watching the live output.

My goal is to achieve unix style tailing of the output.

1 Answer 1

2

I tried several variations with -c and couldn't get it to work. It looks like either I didn't try enough variations, or \watch just doesn't work with psql -c.

I then tried it with psql -f (to read the sql script from a file) and process substitution to provide the sql script "file", and it worked just fine.

For example:

psql --csv -f <(printf '%s\n' \
                 'SELECT * FROM log ORDER BY time DESC LIMIT 200;' \
                 '\watch')

or, if your psql doesn't support the --csv option:

psql -f <(printf '%s\n' \
           '\pset format csv' \
           'SELECT * FROM log ORDER BY time DESC LIMIT 200;' \
           '\watch')

The printf command sends two sql commands to psql (the select, and then the \watch), separated by a newline character (\n). Each of these commands has to be single-quoted separately, so that they are separate arguments to printf.

Note that SQL also uses single-quotes in many commands, so if you need to run an SQL command which uses them, you'll have to be very careful with how you quote your commands. e.g.

psql -f <(printf '%s\n' "select * from table where name='cas'" '\watch 5')

In this example, I'm using double-quotes in the first SQL command to avoid any problems with the quoted string 'cas'. The catch is that double-quotes in shell allow variable interpolation, so you need to escape any shell variables names (e.g. \$var instead of just $var) if you want the literal string rather than the value of the variable. e.g.

psql -f <(printf '%s\n' \
            "select * from table where pass='\$1\$QsHP.S0S\$1Npjv4aKJZv4IjDD.PgNp/'" \
            '\watch 5')

Note that I had to escape every occurence of $ with a \ to prevent the shell from expanding parts of the string that look like they're variable names.

5
  • It doesn't like my TO for some reason.
    – leeand00
    Commented May 3, 2022 at 10:36
  • ERROR: syntax error at or near "TO"
    – leeand00
    Commented May 3, 2022 at 10:41
  • use psql's --csv option. I've edited my answer to demonstrate.
    – cas
    Commented May 3, 2022 at 10:44
  • sigh it doesn't appear to have that option.
    – leeand00
    Commented May 3, 2022 at 11:14
  • 1
    according to man psql (I'm running pg version 14), --csv the option is equivalent to \pset format csv, so try adding that (in single-quotes) as the first printf string, before the select command
    – cas
    Commented May 3, 2022 at 13:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .