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 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

On Thu, Mar 6, 2025 at 4:59 AM Dominique Devienne <ddevienne@gmail.com>
wrote:

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 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

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!

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 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?

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

On Thu, Mar 6, 2025 at 6:11 AM hubert depesz lubaczewski <depesz@depesz.com>
wrote:

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;"

I've used WHERE NOT ()" before. Should have thought of it here.

Thanks.

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

Another dirty hack:

MAGIC=\! psql -Xc "select datname from pg_database WHERE datname $MAGIC~
'template|postgres' ORDER BY datname;"

Em qui., 6 de mar. de 2025 às 10:38, Ron Johnson <ronljohnsonjr@gmail.com>
escreveu:

On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

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

Remove the space:

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

I'm not really sure why as this one works:
psql -c "SELECT ' !'"