Thread

1. psql and regex not like

   Ron Johnson <ronljohnsonjr@gmail.com> — 2025-03-06T09:37:56Z

   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!
   

2. Re: psql and regex not like

   Dominique Devienne <ddevienne@gmail.com> — 2025-03-06T09:59:01Z

   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
   
   
   
   

3. Re: psql and regex not like

   Ron Johnson <ronljohnsonjr@gmail.com> — 2025-03-06T10:24:29Z

   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!
   

4. Re: psql and regex not like

   Dominique Devienne <ddevienne@gmail.com> — 2025-03-06T10:41:42Z

   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
   
   
   
   

5. Re: psql and regex not like

   François Lafont <francois.lafont.1978@gmail.com> — 2025-03-06T10:45:49Z

   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
   
   
   
   
   

6. Re: psql and regex not like

   hubert depesz lubaczewski <depesz@depesz.com> — 2025-03-06T11:11:10Z

   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
   
   
   
   

7. Re: psql and regex not like

   Ron Johnson <ronljohnsonjr@gmail.com> — 2025-03-06T11:34:28Z

   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!
   

8. Re: psql and regex not like

   Renan Alves Fonseca <renanfonseca@gmail.com> — 2025-03-13T21:48:05Z

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

9. Re: psql and regex not like

   Hans Schou <hans.schou@gmail.com> — 2025-03-13T23:09:16Z

   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 ' !'"