MIME-Version: 1.0
References: <CANzqJaCmmqNjSNitxWPekwM7mQesZCfAdXjOprKao8Ted35E-w@mail.gmail.com>
In-Reply-To: <CANzqJaCmmqNjSNitxWPekwM7mQesZCfAdXjOprKao8Ted35E-w@mail.gmail.com>
From: Renan Alves Fonseca <renanfonseca@gmail.com>
Date: Thu, 13 Mar 2025 22:48:05 +0100
Message-ID: <CAN_p2QiMGyJBYyuGjfvofO81p=wJvbYp-x=LgNEX=ueHx_F=nw@mail.gmail.com>
Subject: Re: psql and regex not like
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000093aaa306304047e1"
Archived-At: <https://www.postgresql.org/message-id/CAN_p2QiMGyJBYyuGjfvofO81p%3DwJvbYp-x%3DLgNEX%3DueHx_F%3Dnw%40mail.gmail.com>
Precedence: bulk

--00000000000093aaa306304047e1
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Another dirty hack:

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

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

> This statement runs great from the psql prompt.  Does exactly what I want=
.
> select datname from pg_database WHERE datname !~ 'template|postgres' ORDE=
R
> 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!
>

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

<div dir=3D"ltr">Another dirty hack:<br><br>MAGIC=3D\! psql -Xc &quot;selec=
t datname from pg_database WHERE datname $MAGIC~ &#39;template|postgres&#39=
; ORDER BY datname;&quot;<br></div><br><div class=3D"gmail_quote gmail_quot=
e_container"><div dir=3D"ltr" class=3D"gmail_attr">Em qui., 6 de mar. de 20=
25 =C3=A0s 10:38, Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com=
">ronljohnsonjr@gmail.com</a>&gt; escreveu:<br></div><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex"><div dir=3D"ltr"><div>This statement runs great=
 from the psql prompt.=C2=A0 Does exactly what I want.</div><div><span styl=
e=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 escapi=
ng the &quot;!&quot; generates a bunch of garbage, while escaping throws an=
 sql syntax=C2=A0error.</div><div><br></div><div><font face=3D"monospace">p=
sql -Xc &quot;select datname from pg_database WHERE datname \!~ &#39;templa=
te|postgres&#39; ORDER BY datname;&quot;<br>ERROR: =C2=A0syntax error at or=
 near &quot;\&quot;</font><br></div><div><br></div><div>What&#39;s the magi=
c=C2=A0syntax?</div><div><br></div><div>(Yes, I could create a view and the=
n query the view, but I&#39;m going to be running this remotely against doz=
ens 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 class=3D"gmail_signature_prefix">-- </span><br><d=
iv dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Reda=
cted&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>
</blockquote></div>

--00000000000093aaa306304047e1--