Date: Thu, 6 Mar 2025 12:11:10 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: psql and regex not like
Message-ID: <Z8mCzjdL87tEK4xT@depesz.com>
Reply-To: depesz@depesz.com
References: <CANzqJaCmmqNjSNitxWPekwM7mQesZCfAdXjOprKao8Ted35E-w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <CANzqJaCmmqNjSNitxWPekwM7mQesZCfAdXjOprKao8Ted35E-w@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/Z8mCzjdL87tEK4xT%40depesz.com>
Precedence: bulk

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