Thread: psql and regex not like

On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> This statement runs great from the psql prompt.  Does exactly what I want.
> select datname from pg_database WHERE datname !~ 'template|postgres' ORDER BY datname;
>
> But it doesn't work so well from the bash prompt.  Not escaping the "!" generates a bunch of garbage, while escaping
throwsan sql syntax error. 
>
> psql -Xc "select datname from pg_database WHERE datname \!~ 'template|postgres' ORDER BY datname;"
> ERROR:  syntax error at or near "\"
>
> What's the magic syntax?
>
> (Yes, I could create a view and then query the view, but I'm going to be running this remotely against dozens of
servers,so I don't want to have to create dozens of views, then need to recreate them every time I want to change the
query.)

No answer to your question, but I'd argue it's moot, because it's not
the right query in the first place :)
It should be instead, IMHO, the one below, which should be OK in BASH
syntax-wise. --DD

select datname from pg_database WHERE datistemplate = false and
datname <> 'postgres' order by 1

On Thu, Mar 6, 2025 at 11:24 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> I already do that.  This is part of a long chain of commands so I'm trying to minimize the length of commands.

but given that your regexp patterns are not anchored, they are not
equivalent. I think mine is "more correct".

> Anyway, it would be good to know the answer for any future queries that need multiple exclusions.

Sure. First, it works fine with TCSH :). I repro a (different) failure
in BASH. But the below works fine for me:

psql "service=acme" -Xc 'select datname from pg_database where datname
!~ $$(template|postgres)$$ order by 1'

i.e. use single-quotes, and an inner $$ literal. One of 3 options an
AI chatbot gave me. --DD

Hi,

On 3/6/25 10:37, Ron Johnson wrote:
> This statement runs great from the psql prompt.  Does exactly what I want.
> select datname from pg_database WHERE datname !~ 'template|postgres' ORDER BY datname;
> 
> But it doesn't work so well from the bash prompt.  Not escaping the "!" generates a bunch of garbage, while escaping
throwsan sql syntax error.
 
> 
> psql -Xc "select datname from pg_database WHERE datname \!~ 'template|postgres' ORDER BY datname;"
> ERROR:  syntax error at or near "\"
> 
> What's the magic syntax?

Indeed it's a question about bash.

This works well for me:

     set +H
     psql -Xc "SELECT datname FROM pg_database WHERE datname !~ 'template|postgres' ORDER BY datname;"

~$ echo "!~"
-bash: !~: event not found

~$ set +H

~$ echo "!~"
!~

Bye.

-- 
François Lafont

On Thu, Mar 06, 2025 at 04:37:56AM -0500, Ron Johnson wrote:
> This statement runs great from the psql prompt.  Does exactly what I want.
> select datname from pg_database WHERE datname !~ 'template|postgres' ORDER
> BY datname;
> But it doesn't work so well from the bash prompt.  Not escaping the "!"
> generates a bunch of garbage, while escaping throws an sql syntax error.

The problem is that ! is magical in bash.

The solution is to not use it. Instead you can easily do:

psql -Xc "select datname from pg_database WHERE not datname ~ 'template|postgres' ORDER BY datname;"

Best regards,

depesz