1 文章背景在使用 PostgreSQL 时除了常规的增删改查还经常需要查询数据库中的元数据信息例如数据库列表、Schema 列表、表列表、字段信息、约束信息等。本文主要整理 PostgreSQL 中常见的元数据查询语句以及数据库、Schema、表、字段、约束和视图相关的 DDL 操作。2 查询数据库用户2.1 查询所有用户名称SELECTusenameFROMpg_shadow;2.2 查询用户完整信息SELECT*FROMpg_shadow;常见字段含义如下①usename用户名。②usesysid用户系统 ID。③usecreatedb是否有创建数据库权限。④usesuper是否是超级用户。⑤userepl是否有复制权限。⑥usebypassrls是否可以绕过行级安全策略。⑦passwd用户密码信息。⑧valuntil用户权限有效期。需要注意pg_shadow涉及用户敏感信息实际使用时应注意权限控制。3 查询数据库信息3.1 查询所有非模板数据库名称SELECTdatnameFROMpg_databaseWHEREdatistemplatefalse;3.2 查询所有非模板数据库完整信息SELECT*FROMpg_databaseWHEREdatistemplatefalse;常见字段含义如下①oid数据库对象 ID。②datname数据库名称。③datdba数据库所有者 ID。④encoding数据库编码。⑤datistemplate是否为模板数据库。⑥datallowconn是否允许连接。⑦datconnlimit最大连接数。⑧dattablespace数据库使用的表空间。⑨datcollate排序规则。⑩datctype字符分类规则。4 查询 Schema 信息查询当前数据库中的所有 SchemaSELECTschema_nameFROMinformation_schema.schemata;常见字段包括①catalog_name所属数据库名称。②schema_nameSchema 名称。③schema_ownerSchema 所有者。④sql_pathSQL 路径信息。5 查询表信息5.1 查询 public 下所有表SELECTtable_nameFROMinformation_schema.tablesWHEREtable_schemapublic;也可以使用 PostgreSQL 系统表SELECT*FROMpg_tablesWHEREschemanamepublic;5.2 information_schema.tables 常见字段①table_catalog表所属数据库。②table_schema表所属 Schema。③table_name表名。④table_type表类型。⑤is_insertable_into是否可以插入数据。6 查询字段信息查询某张表的全部字段信息SELECT*FROMinformation_schema.columnsWHEREtable_schemapublicANDtable_nameuser_info;常见字段含义如下①table_catalog所属数据库。②table_schema所属 Schema。③table_name所属表名。④column_name字段名。⑤ordinal_position字段顺序。⑥column_default字段默认值。⑦is_nullable是否可以为空。⑧data_type数据类型。⑨character_maximum_length字符最大长度。⑩numeric_precision数值精度。7 创建和修改数据库7.1 创建数据库CREATEDATABASEdatabase_name;7.2 指定数据库所有者CREATEDATABASEdatabase_nameWITHOWNER username;7.3 修改数据库所有者ALTERDATABASEdatabase_name OWNERTOusername;7.4 修改数据库名称ALTERDATABASEold_database_nameRENAMETOnew_database_name;8 创建和修改 Schema8.1 创建 SchemaCREATESCHEMAschema_name;8.2 修改 Schema 名称ALTERSCHEMAold_schema_nameRENAMETOnew_schema_name;8.3 修改 Schema 所有者ALTERSCHEMAschema_name OWNERTOusername;9 创建和修改表9.1 创建表CREATETABLEpublic.user_info(id BIGSERIALPRIMARYKEY,user_nameVARCHAR(20)NOTNULL,user_passwordVARCHAR(30)NOTNULL);9.2 修改表名ALTERTABLEpublic.user_infoRENAMETOuser_base_info;9.3 修改表所有者ALTERTABLEpublic.user_base_info OWNERTOusername;10 修改字段信息10.1 修改字段名称ALTERTABLEpublic.user_infoRENAMECOLUMNold_nameTOnew_name;10.2 修改字段类型ALTERTABLEpublic.user_infoALTERCOLUMNuser_nameTYPEVARCHAR(30);10.3 设置字段默认值ALTERTABLEpublic.user_infoALTERCOLUMNuser_nameSETDEFAULTzhoubin;10.4 删除字段默认值ALTERTABLEpublic.user_infoALTERCOLUMNuser_nameDROPDEFAULT;11 主键约束11.1 查询主键约束名称SELECTconstraint_nameFROMinformation_schema.table_constraintsWHEREtable_schemapublicANDtable_nameuser_infoANDconstraint_typePRIMARY KEY;11.2 添加主键ALTERTABLEpublic.user_infoADDCONSTRAINTuser_info_pkeyPRIMARYKEY(id);11.3 删除主键ALTERTABLEpublic.user_infoDROPCONSTRAINTuser_info_pkey;12 非空约束12.1 查询字段是否可为空SELECTcolumn_name,is_nullableFROMinformation_schema.columnsWHEREtable_schemapublicANDtable_nameuser_info;12.2 设置非空ALTERTABLEpublic.user_infoALTERCOLUMNuser_nameSETNOTNULL;12.3 删除非空ALTERTABLEpublic.user_infoALTERCOLUMNuser_nameDROPNOTNULL;13 唯一约束13.1 查询唯一约束SELECTconstraint_nameFROMinformation_schema.table_constraintsWHEREtable_schemapublicANDtable_nameuser_infoANDconstraint_typeUNIQUE;13.2 添加唯一约束ALTERTABLEpublic.user_infoADDCONSTRAINTuser_info_uniqueUNIQUE(user_name);13.3 删除唯一约束ALTERTABLEpublic.user_infoDROPCONSTRAINTuser_info_unique;14 检查约束14.1 创建检查约束建表时创建CREATETABLEpublic.user_info(idSERIALPRIMARYKEY,user_nameVARCHAR(20),ageINTEGERCHECK(age18));建表后添加ALTERTABLEpublic.user_infoADDCONSTRAINTcheck_ageCHECK(age18);14.2 查询检查约束SELECTconstraint_nameFROMinformation_schema.table_constraintsWHEREtable_schemapublicANDtable_nameuser_infoANDconstraint_typeCHECK;14.3 删除检查约束ALTERTABLEpublic.user_infoDROPCONSTRAINTcheck_age;15 视图操作15.1 创建视图CREATEVIEWuser_password_viewASSELECTuser_passwordFROMpublic.user_infoWHEREuser_namezhoubin;15.2 查询视图SELECT*FROMuser_password_view;15.3 修改视图CREATEORREPLACEVIEWuser_password_viewASSELECTuser_name,user_passwordFROMpublic.user_infoWHEREuser_namezhoubin;视图可以理解为一个保存好的查询结果结构。它本身不一定存储真实数据而是基于底层表进行查询。16 小结PostgreSQL 提供了丰富的系统表和information_schema视图用于查看数据库内部对象信息。实际开发中如果需要动态获取表名、字段名、约束名或者做数据库管理工具这些元数据查询语句会非常常用。