PostgreSQL | Database | List Created Databases

Tags:

This page explains how to list databases created in PostgreSQL.

Use the \l Command

First, here is how to use a psql meta-command. To retrieve the list of created databases, run the following.

postgres=# \l
                                      데이터베이스 목록
   이름    |  소유주  | 인코딩 |     Collate      |      Ctype       |      액세스 권한
-----------+----------+--------+------------------+------------------+-----------------------
 devkuma   | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 devkuma2  | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 postgres  | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 sample    | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
 template0 | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |        |                  |                  | postgres=CTc/postgres
 template1 | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
           |          |        |                  |                  | postgres=CTc/postgres
(6개 행)


postgres=#

You can view the list of created databases.

Query the pg_database System Catalog

Next, look at how to get this information from pg_database, one of PostgreSQL’s system catalogs. System catalogs manage information such as databases and tables in tables used by PostgreSQL’s management system. The pg_database catalog has the following columns.

Name Type Description
oid oid Identifier. It cannot be obtained unless explicitly specified.
datname name Database name
datdba oid Database owner
encoding int4 Identifier number of the database character set
datcollate name Database string sort order
datctype name Database character classification
datistemplate bool Whether all users can clone it. If false, only a superuser or the database owner can clone it.
datallowconn bool Whether connections to the database are allowed. Used to prevent changes to data in template0.
datconnlimit int4 Maximum number of concurrent connections. -1 means unlimited.
datlastsysoid oid Final system OID in the database
datfrozenxid xid Transaction IDs older than this value in this database have been replaced so that they have permanent, frozen transaction IDs. This is used to track whether vacuum work should be performed to prevent transaction ID wraparound problems for this database and to shrink pg_xact.
datminmxid xid All multitransaction IDs before they are replaced by transaction IDs in this database. This is used to track whether the database should be vacuumed to prevent transaction ID wraparound problems or to shrink pg_multixact.
dattablespace oid Default tablespace for the database
datacl aclitem [] Access privilege list

This time, query the following columns from pg_database.

postgres=# select datname, datdba, encoding, datcollate, datctype from pg_database;
  datname  | datdba | encoding |    datcollate    |     datctype
-----------+--------+----------+------------------+------------------
 postgres  |     10 |        6 | Korean_Korea.949 | Korean_Korea.949
 template1 |     10 |        6 | Korean_Korea.949 | Korean_Korea.949
 template0 |     10 |        6 | Korean_Korea.949 | Korean_Korea.949
 sample    |     10 |        6 | Korean_Korea.949 | Korean_Korea.949
 devkuma   |     10 |        6 | Korean_Korea.949 | Korean_Korea.949
 devkuma2  |     10 |        6 | Korean_Korea.949 | Korean_Korea.949
(6개 행)


postgres=#

You can see that the list of created databases has been retrieved.

Here, datdba, which represents the owner of the database, displays the oid value from the pg_authid system catalog that manages role data. To get the actual role name, refer to rolname in pg_authid. Also, encoding, which represents the character set, can be converted to the actual character set name by using the pg_encoding_to_char function.

For example, run the following.

postgres=# select datname,
postgres-#     pg_authid.rolname as dbrollname,
postgres-#     pg_encoding_to_char(encoding) as dbencoding,
postgres-#     datcollate,
postgres-#     datctype
postgres-#   from pg_database
postgres-#   join pg_authid on pg_authid.oid = pg_database.datdba;
  datname  | dbrollname | dbencoding |    datcollate    |     datctype
-----------+------------+------------+------------------+------------------
 devkuma2  | postgres   | UTF8       | Korean_Korea.949 | Korean_Korea.949
 devkuma   | postgres   | UTF8       | Korean_Korea.949 | Korean_Korea.949
 sample    | postgres   | UTF8       | Korean_Korea.949 | Korean_Korea.949
 template0 | postgres   | UTF8       | Korean_Korea.949 | Korean_Korea.949
 template1 | postgres   | UTF8       | Korean_Korea.949 | Korean_Korea.949
 postgres  | postgres   | UTF8       | Korean_Korea.949 | Korean_Korea.949
(6개 행)


postgres=#

You can list databases together with the database owner and character set.

–

This page explained how to retrieve the list of created databases.

References