public inbox for [email protected]help / color / mirror / Atom feed
Re: psql and regex not like 3+ messages / 2 participants [nested] [flat]
* Re: psql and regex not like @ 2025-03-06 09:59 Dominique Devienne <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Dominique Devienne @ 2025-03-06 09:59 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: psql and regex not like @ 2025-03-06 10:24 Ron Johnson <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ron Johnson @ 2025-03-06 10:24 UTC (permalink / raw) To: pgsql-general On Thu, Mar 6, 2025 at 4:59 AM Dominique Devienne <[email protected]> wrote: > On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson <[email protected]> > 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! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: psql and regex not like @ 2025-03-06 10:41 Dominique Devienne <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Dominique Devienne @ 2025-03-06 10:41 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general On Thu, Mar 6, 2025 at 11:24 AM Ron Johnson <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-06 10:41 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-06 09:59 Re: psql and regex not like Dominique Devienne <[email protected]> 2025-03-06 10:24 ` Ron Johnson <[email protected]> 2025-03-06 10:41 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox