MIME-Version: 1.0
References: <CANzqJaCmmqNjSNitxWPekwM7mQesZCfAdXjOprKao8Ted35E-w@mail.gmail.com>
 <Z8mCzjdL87tEK4xT@depesz.com>
In-Reply-To: <Z8mCzjdL87tEK4xT@depesz.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 6 Mar 2025 06:34:28 -0500
Message-ID: <CANzqJaA9ecP_CWWnaLd5agfZEJZ-Tk1joxk3w4wJJXbzuPw1Ww@mail.gmail.com>
Subject: Re: psql and regex not like
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002753d9062faae42e"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaA9ecP_CWWnaLd5agfZEJZ-Tk1joxk3w4wJJXbzuPw1Ww%40mail.gmail.com>
Precedence: bulk

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

On Thu, Mar 6, 2025 at 6:11=E2=80=AFAM hubert depesz lubaczewski <depesz@de=
pesz.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.

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Mar 6, 2025 at 6:11=E2=80=AFAM hu=
bert depesz lubaczewski &lt;<a href=3D"mailto:depesz@depesz.com">depesz@dep=
esz.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container=
"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, Mar 06, 2025 at=
 04:37:56AM -0500, Ron Johnson wrote:<br>
&gt; This statement runs great from the psql prompt.=C2=A0 Does exactly wha=
t I want.<br>
&gt; select datname from pg_database WHERE datname !~ &#39;template|postgre=
s&#39; ORDER<br>
&gt; BY datname;<br>
&gt; But it doesn&#39;t work so well from the bash prompt.=C2=A0 Not escapi=
ng the &quot;!&quot;<br>
&gt; generates a bunch of garbage, while escaping throws an sql syntax erro=
r.<br>
<br>
The problem is that ! is magical in bash.<br>
<br>
The solution is to not use it. Instead you can easily do:<br>
<br>
psql -Xc &quot;select datname from pg_database WHERE not datname ~ &#39;tem=
plate|postgres&#39; ORDER BY datname;&quot;<br></blockquote><div><br></div>=
<div>I&#39;ve used WHERE NOT ()&quot; before.=C2=A0 Should have thought of =
it here.</div><div><br></div><div>Thanks.</div><div><br></div></div><span c=
lass=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gma=
il_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>

--0000000000002753d9062faae42e--