MIME-Version: 1.0
References: <CANzqJaCmmqNjSNitxWPekwM7mQesZCfAdXjOprKao8Ted35E-w@mail.gmail.com>
 <CAFCRh-8QB7PQS_ny7GEgxBU_VLkAuj-Wwk_vtsM5bivWc-HbXg@mail.gmail.com>
In-Reply-To: <CAFCRh-8QB7PQS_ny7GEgxBU_VLkAuj-Wwk_vtsM5bivWc-HbXg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 6 Mar 2025 05:24:29 -0500
Message-ID: <CANzqJaBaottzqJm6MjP_tuATRs81vtBanjn1Ba0sY==7FoUjXw@mail.gmail.com>
Subject: Re: psql and regex not like
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f5bd88062fa9e9a3"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBaottzqJm6MjP_tuATRs81vtBanjn1Ba0sY%3D%3D7FoUjXw%40mail.gmail.com>
Precedence: bulk

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

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

> On Thu, Mar 6, 2025 at 10:38=E2=80=AFAM 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 b=
e
> 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 =3D 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.

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

--000000000000f5bd88062fa9e9a3
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 4:59=E2=80=AFAM Do=
minique Devienne &lt;<a href=3D"mailto:ddevienne@gmail.com">ddevienne@gmail=
.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;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex">On Thu, Mar 6, 2025 at 10:=
38=E2=80=AFAM Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com" ta=
rget=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; 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 BY datname;<br>
&gt;<br>
&gt; 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 error.<br>
&gt;<br>
&gt; psql -Xc &quot;select datname from pg_database WHERE datname \!~ &#39;=
template|postgres&#39; ORDER BY datname;&quot;<br>
&gt; ERROR:=C2=A0 syntax error at or near &quot;\&quot;<br>
&gt;<br>
&gt; What&#39;s the magic syntax?<br>
&gt;<br>
&gt; (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, then need to recreate them every time I=
 want to change the query.)<br>
<br>
No answer to your question, but I&#39;d argue it&#39;s moot, because it&#39=
;s not<br>
the right query in the first place :)<br>
It should be instead, IMHO, the one below, which should be OK in BASH<br>
syntax-wise. --DD<br>
<br>
select datname from pg_database WHERE datistemplate =3D false and<br>
datname &lt;&gt; &#39;postgres&#39; order by 1<br>
</blockquote></div><div><br clear=3D"all"></div><div>I already do that.=C2=
=A0 This is part of a long chain of commands so I&#39;m trying to minimize =
the length of commands.</div><div><br></div><div>Anyway, it would be good t=
o know the answer for any future queries that need multiple exclusions.</di=
v><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div =
dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacte=
d&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><di=
v><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000f5bd88062fa9e9a3--