I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them:
psql -l -A -t
but the output shows an obvious issue: the records are separated by newlines, but also contain newlines.
$ psql -l -A -t postgres|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8| template0|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres postgres=CTc/postgres template1|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres postgres=CTc/postgres
Using the -R option I can change the record separator, but it seems like no matter what I change it to, there's the risk of that string appearing in the data. Is it possible to instead tell psql to replace the newlines in the data with something else? (and then what if that string also appears in the data?)
I'd also tried to set the record separator to a null character with such sequences as
-R '\000' and
-R "\0", but it doesn't seem to interpret escape sequences in the parameter at all, and just uses the literal string
The other option I know of to list all databases is:
psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"
but that requires me to give the password for the postgres user, so it's not desirable. Perhaps there's another way to get a list of the names of all databases?