Subject: Re: psql and regex not like

From: Dominique Devienne <[email-masked]>
Date: Thu, 6 Mar 2025 10:59:01 +0100

On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson <[email-masked]> 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.
>
> 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

---

From: Ron Johnson <[email-masked]>
Date: Thu, 6 Mar 2025 05:24:29 -0500

On Thu, Mar 6, 2025 at 4:59 AM Dominique Devienne <[email-masked]>
wrote:

> > 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.
> >
> > (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.)
>

I already do that.  This is part of a long chain of commands so I'm trying
to minimize the length of commands.

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

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Dominique Devienne <[email-masked]>
Date: Thu, 6 Mar 2025 11:41:42 +0100

On Thu, Mar 6, 2025 at 11:24 AM Ron Johnson <[email-masked]> 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