pg_database

A system catalogue table storing information about all databases

pg_database is a system catalogue table storing information about all databases.

This table is shared across all databases in a cluster.

psql command

  • \l lists the databases contained in pg_database
  • \l+ additionally displays the database sizes.

Notes

From PostgreSQL 11, background worker processes are able to attach to databases where datallowconn = false (commit eed1ce72).

Definition by PostgreSQL version

pg_database (PostgreSQL 13)

               Table "pg_catalog.pg_database"
    Column     |   Type    | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
 oid           | oid       |           | not null |
 datname       | name      |           | not null |
 datdba        | oid       |           | not null |
 encoding      | integer   |           | not null |
 datcollate    | name      |           | not null |
 datctype      | name      |           | not null |
 datistemplate | boolean   |           | not null |
 datallowconn  | boolean   |           | not null |
 datconnlimit  | integer   |           | not null |
 datlastsysoid | oid       |           | not null |
 datfrozenxid  | xid       |           | not null |
 datminmxid    | xid       |           | not null |
 dattablespace | oid       |           | not null |
 datacl        | aclitem[] |           |          |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 12)

               Table "pg_catalog.pg_database"
    Column     |   Type    | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
 oid           | oid       |           | not null |
 datname       | name      |           | not null |
 datdba        | oid       |           | not null |
 encoding      | integer   |           | not null |
 datcollate    | name      |           | not null |
 datctype      | name      |           | not null |
 datistemplate | boolean   |           | not null |
 datallowconn  | boolean   |           | not null |
 datconnlimit  | integer   |           | not null |
 datlastsysoid | oid       |           | not null |
 datfrozenxid  | xid       |           | not null |
 datminmxid    | xid       |           | not null |
 dattablespace | oid       |           | not null |
 datacl        | aclitem[] |           |          |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 11)

               Table "pg_catalog.pg_database"
    Column     |   Type    | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
 datname       | name      |           | not null |
 datdba        | oid       |           | not null |
 encoding      | integer   |           | not null |
 datcollate    | name      |           | not null |
 datctype      | name      |           | not null |
 datistemplate | boolean   |           | not null |
 datallowconn  | boolean   |           | not null |
 datconnlimit  | integer   |           | not null |
 datlastsysoid | oid       |           | not null |
 datfrozenxid  | xid       |           | not null |
 datminmxid    | xid       |           | not null |
 dattablespace | oid       |           | not null |
 datacl        | aclitem[] |           |          |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 10)

               Table "pg_catalog.pg_database"
    Column     |   Type    | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
 datname       | name      |           | not null |
 datdba        | oid       |           | not null |
 encoding      | integer   |           | not null |
 datcollate    | name      |           | not null |
 datctype      | name      |           | not null |
 datistemplate | boolean   |           | not null |
 datallowconn  | boolean   |           | not null |
 datconnlimit  | integer   |           | not null |
 datlastsysoid | oid       |           | not null |
 datfrozenxid  | xid       |           | not null |
 datminmxid    | xid       |           | not null |
 dattablespace | oid       |           | not null |
 datacl        | aclitem[] |           |          |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 9.6)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 datminmxid    | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 9.5)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 datminmxid    | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 9.4)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 datminmxid    | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 9.3)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 datminmxid    | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 9.2)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 9.1)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 9.0)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 8.4)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datconfig     | text[]    |
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Triggers:
    pg_sync_pg_database AFTER INSERT OR DELETE OR UPDATE ON pg_database FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger()
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 8.3)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datconfig     | text[]    |
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Triggers:
    pg_sync_pg_database AFTER INSERT OR DELETE OR UPDATE ON pg_database FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger()
Tablespace: "pg_global"
    

Documentation: pg_database

pg_database (PostgreSQL 8.2)

    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datconfig     | text[]    |
 datacl        | aclitem[] |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Triggers:
    pg_sync_pg_database AFTER INSERT OR DELETE OR UPDATE ON pg_database FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger()
Tablespace: "pg_global"
    

Documentation: pg_database

Change history

Note: any changes before PostgreSQL 7.2 are not recorded in the above list.

Examples

postgres=# SELECT * from pg_database WHERE datname='postgres'\x\g\x
Expanded display is on.
-[ RECORD 1 ]-+------------
oid           | 13587
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | en_US.UTF-8
datctype      | en_US.UTF-8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 13586
datfrozenxid  | 477
datminmxid    | 1
dattablespace | 1663
datacl        | 

Categories

Cluster-wide table, System catalogue

See also

pg_stat_database, CREATE DATABASE, DROP DATABASE