MIME-Version: 1.0
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 6 Mar 2025 04:37:56 -0500
Message-ID: <CANzqJaCmmqNjSNitxWPekwM7mQesZCfAdXjOprKao8Ted35E-w@mail.gmail.com>
Subject: psql and regex not like
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000070da46062fa943ce"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCmmqNjSNitxWPekwM7mQesZCfAdXjOprKao8Ted35E-w%40mail.gmail.com>
Precedence: bulk

--00000000000070da46062fa943ce
Content-Type: text/plain; charset="UTF-8"

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

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

--00000000000070da46062fa943ce
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>This statement runs great from the psql prompt.=C2=A0=
 Does exactly what I want.</div><div><span style=3D"font-family:monospace">=
select datname from pg_database WHERE datname !~ &#39;template|postgres&#39=
; ORDER BY datname;</span></div><div><br></div><div>But it doesn&#39;t work=
 so well from the bash prompt.=C2=A0 Not escaping the &quot;!&quot; generat=
es a bunch of garbage, while escaping throws an sql syntax=C2=A0error.</div=
><div><br></div><div><font face=3D"monospace">psql -Xc &quot;select datname=
 from pg_database WHERE datname \!~ &#39;template|postgres&#39; ORDER BY da=
tname;&quot;<br>ERROR: =C2=A0syntax error at or near &quot;\&quot;</font><b=
r></div><div><br></div><div>What&#39;s the magic=C2=A0syntax?</div><div><br=
></div><div>(Yes, I could create a view and then query the view, but I&#39;=
m going to be running this remotely against dozens of servers, so I don&#39=
;t want to have to create dozens of views,=C2=A0then need to recreate them =
every time I want to change the=C2=A0query.)</div><div><br></div><span clas=
s=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_=
signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr">Death to &lt=
;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive=
.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div=
>

--00000000000070da46062fa943ce--