pg_database

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

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

pg_database 一直存在于 PostgreSQL 中。

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

psql 命令

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

备注

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

按 PostgreSQL 版本定义

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

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

更改历史记录

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        |

列 encoding 字段中包含的整数值可以使用函数 pg_encoding_to_char() 转换为人类可读的字符串。

参考

PostgreSQL 文档： pg_database

另请参阅

pg_stat_database，CREATE DATABASE，ALTER DATABASE，DROP DATABASE，createdb，dropdb

反馈

提交任何关于 "pg_database" 的评论、建议或更正 此处。

版本图表

有用概述

有用链接

咖啡

目录

pg_database

psql 命令

备注

按 PostgreSQL 版本定义

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)

更改历史记录

示例

参考

分类

另请参阅

反馈