List databases

List the databases on a server with psql -l, and get just the names for a script by reading the first column of psql -lqt without the headers.

1 min read

Summarize in AI

You want the names of the databases on a server, and psql -l prints a wide table.

Shell

psql -lqt | cut -d'|' -f1 | sed 's/ //g' | grep .
echo ---
psql -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate ORDER BY 1"

Text

postgres
template0
template1
---
postgres

-l prints the list of databases and exits, and -q -t remove the extras so that each line is a row with columns separated by a pipe, as the psql reference describes. Cutting the first column gives the names, and it includes the two template databases. The query on pg_database is the exact form: it can filter templates with datistemplate.

Parsing the listing breaks if the table format changes between versions, so prefer the query in a script. A database with datallowconn set to false still appears in both outputs. Connecting needs a database to exist; postgres is the usual one, and -d template1 is the fallback.

Taggeddatabases postgresql scripting

More snippets

Databases

Dump one table

Dump one table with pg_dump -t name. The option takes a pattern, so -t orders does not match orders_archive but -t 'orders*' does, and quoting rules apply.

2 min read

Databases

Run SQL from a file

Run SQL from a file with psql -f. An error in the middle does not stop the file and the exit status stays 0, unless ON_ERROR_STOP is set to 1.

2 min read

Databases

Describe a table

Show a table's columns, indexes and foreign keys with the psql meta-command backslash d, and add a plus sign for storage and comments.

2 min read

More snippets

Dump one table

Run SQL from a file

Describe a table

Get new posts by email