pg_database

一个存储所有数据库信息的系统表

pg_database 是一个系统目录表，存储有关所有数据库的信息。

pg_database 一直存在于 PostgreSQL 中。

该表在集群的所有数据库之间共享。

psql 命令

\l 列出 pg_database 中包含的数据库
\l+ 额外显示数据库的大小。

备注

从PostgreSQL 11开始，后台工作进程能够连接到 datallowconn = false 的数据库（提交 eed1ce72）。

按 PostgreSQL 版本定义

pg_database (PostgreSQL 19)

               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 | 
 datlocprovider | "char"    |           | not null | 
 datistemplate  | boolean   |           | not null | 
 datallowconn   | boolean   |           | not null | 
 dathasloginevt | boolean   |           | not null | 
 datconnlimit   | integer   |           | not null | 
 datfrozenxid   | xid       |           | not null | 
 datminmxid     | xid       |           | not null | 
 dattablespace  | oid       |           | not null | 
 datcollate     | text      | C         | not null | 
 datctype       | text      | C         | not null | 
 datlocale      | text      | C         |          | 
 daticurules    | text      | C         |          | 
 datcollversion | text      | C         |          | 
 datacl         | aclitem[] |           |          | 
Indexes:
    "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_database

pg_database (PostgreSQL 18)

               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 | 
 datlocprovider | "char"    |           | not null | 
 datistemplate  | boolean   |           | not null | 
 datallowconn   | boolean   |           | not null | 
 dathasloginevt | boolean   |           | not null | 
 datconnlimit   | integer   |           | not null | 
 datfrozenxid   | xid       |           | not null | 
 datminmxid     | xid       |           | not null | 
 dattablespace  | oid       |           | not null | 
 datcollate     | text      | C         | not null | 
 datctype       | text      | C         | not null | 
 datlocale      | text      | C         |          | 
 daticurules    | text      | C         |          | 
 datcollversion | text      | C         |          | 
 datacl         | aclitem[] |           |          | 
Indexes:
    "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_database

pg_database (PostgreSQL 17)

               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 | 
 datlocprovider | "char"    |           | not null | 
 datistemplate  | boolean   |           | not null | 
 datallowconn   | boolean   |           | not null | 
 dathasloginevt | boolean   |           | not null | 
 datconnlimit   | integer   |           | not null | 
 datfrozenxid   | xid       |           | not null | 
 datminmxid     | xid       |           | not null | 
 dattablespace  | oid       |           | not null | 
 datcollate     | text      | C         | not null | 
 datctype       | text      | C         | not null | 
 datlocale      | text      | C         |          | 
 daticurules    | text      | C         |          | 
 datcollversion | text      | C         |          | 
 datacl         | aclitem[] |           |          | 
Indexes:
    "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_database

pg_database (PostgreSQL 16)

               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 | 
 datlocprovider | "char"    |           | not null | 
 datistemplate  | boolean   |           | not null | 
 datallowconn   | boolean   |           | not null | 
 datconnlimit   | integer   |           | not null | 
 datfrozenxid   | xid       |           | not null | 
 datminmxid     | xid       |           | not null | 
 dattablespace  | oid       |           | not null | 
 datcollate     | text      | C         | not null | 
 datctype       | text      | C         | not null | 
 daticulocale   | text      | C         |          | 
 daticurules    | text      | C         |          | 
 datcollversion | text      | C         |          | 
 datacl         | aclitem[] |           |          | 
Indexes:
    "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_database

pg_database (PostgreSQL 15)

               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 | 
 datlocprovider | "char"    |           | not null | 
 datistemplate  | boolean   |           | not null | 
 datallowconn   | boolean   |           | not null | 
 datconnlimit   | integer   |           | not null | 
 datfrozenxid   | xid       |           | not null | 
 datminmxid     | xid       |           | not null | 
 dattablespace  | oid       |           | not null | 
 datcollate     | text      | C         | not null | 
 datctype       | text      | C         | not null | 
 daticulocale   | text      | C         |          | 
 datcollversion | text      | C         |          | 
 datacl         | aclitem[] |           |          | 
Indexes:
    "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_database

pg_database (PostgreSQL 14)

               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_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

文档: pg_database

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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: 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"

文档: pg_database

可以pg_encoding_to_char() 函数将 encoding 列中的整数值转换为人类可读的表示形式。

变更历史

PostgreSQL 17
- 添加了 dathasloginevt 列（提交 e83d1b0c）
- daticulocale 列重命名为 datlocale （提交 f696c0cd）
PostgreSQL 16
- 添加了 daticurules 列（提交 30a53b79）
PostgreSQL 15
- 添加了 datcollversion 列（提交 37851a8b）
- 添加了 daticulocale 列（提交 f2553d43）
- datcollate 列从 name 更改为 text （提交 54637508）
- datctype 列从 name 更改为 text （提交 54637508）
- 删除了 datlastsysoid 列（提交 ab4fd4f8）
PostgreSQL 9.3
- 添加了 datminmxid 列（提交 0ac5ad51）
PostgreSQL 9.0
- 删除了 datconfig 列，并被新表 pg_db_role_setting 取代（提交 2eda8dfb）
- 删除了 pg_sync_pg_database 触发器（提交 a8bb8eb5）
PostgreSQL 8.4
- 添加了 datcollate 列（提交 61d96749）
- 添加了 dattype 列（提交 61d96749）
PostgreSQL 8.2
- 删除了 datvacuumxid 列（提交 48188e16）
PostgreSQL 8.0
- 添加了 dattablespace 列（提交 2467394e）
- 删除了 datpath 列（提交 2467394e）
PostgreSQL 7.3
- 添加了 datconfig 列（提交 1aac2c85）
- 添加了 datacl 列（提交 b0bcf8aa）
PostgreSQL 7.2
- 添加了 datvacuumxid 列（提交 bc7d37a5）
- 添加了 datfrozenxid 列（提交 bc7d37a5）
PostgreSQL 6.4
- 添加了 encoding 列（提交 bf00bbb0）

注意：PostgreSQL 7.2 之前的某些更改可能未在上述列表中记录。

示例

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        |

可以通过函数 pg_encoding_to_char() 将 encoding 字段中的整数值转换为人类可读的字符串。

参考资料

PostgreSQL 文档: pg_database

另请参阅

pg_stat_database, CREATE DATABASE, ALTER DATABASE, DROP DATABASE, createdb, dropdb

反馈

就关于 "pg_database" 的任何评论、建议或更正，请在此提交。

版本图表

实用概述

有用链接

咖啡

目录

pg_database

psql 命令

备注

按 PostgreSQL 版本定义

pg_database (PostgreSQL 19)

pg_database (PostgreSQL 18)

pg_database (PostgreSQL 17)

pg_database (PostgreSQL 16)

pg_database (PostgreSQL 15)

pg_database (PostgreSQL 14)

pg_database (PostgreSQL 13)

pg_database (PostgreSQL 12)

pg_database (PostgreSQL 11)

pg_database (PostgreSQL 10)

pg_database (PostgreSQL 9.6)

pg_database (PostgreSQL 9.5)

pg_database (PostgreSQL 9.4)

pg_database (PostgreSQL 9.3)

pg_database (PostgreSQL 9.2)

pg_database (PostgreSQL 9.1)

pg_database (PostgreSQL 9.0)

pg_database (PostgreSQL 8.4)

pg_database (PostgreSQL 8.3)

pg_database (PostgreSQL 8.2)

变更历史

示例

参考资料

分类

另请参阅

反馈