查看当前数据库
postgres=# select current_database(); current_database ------------------ postgres (1 row)
查看数据库用户列表
postgres=# select * from pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | rolresqueue | oid | rolcreaterextgpfd | rolcreaterexthttp | rolcreatewextgpfd | rolresgroup ---------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+------------ -+-------+-------------------+-------------------+-------------------+------------- gpadmin | t | t | t | t | t | t | t | -1 | ******** | | | 6055 | 10 | t | t | t | 6438 gpmon | t | t | f | t | t | t | f | -1 | ******** | | | 6055 | 16384 | f | f | f | 6438 gpcc_basic | f | t | f | f | f | f | f | -1 | ******** | | | 6055 | 16893 | f | f | f | 6437 gpcc_operator | f | t | f | f | f | f | f | -1 | ******** | | | 6055 | 16894 | f | f | f | 6437 gpcc_operator_basic | f | t | f | f | f | f | f | -1 | ******** | | | 6055 | 16895 | f | f | f | 6437 (5 rows)
postgres=# select * from pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ---------+----------+-------------+----------+-----------+---------+----------+----------+----------- gpadmin | 10 | t | t | t | t | ******** | | gpmon | 16384 | t | t | t | f | ******** | | (2 rows)
查看所有内置字典
postgres=# select * from pg_ Display all 130 possibilities? (y or n) pg_aggregate pg_depend pg_partition_encoding pg_shdescription pg_stat_sys_indexes pg_am pg_description pg_partition_rule pg_shseclabel pg_stat_sys_tables pg_amop pg_enum pg_partitions pg_stat_activity pg_stat_user_functions pg_amproc pg_event_trigger pg_partition_templates pg_stat_all_indexes pg_stat_user_indexes pg_aoseg. pg_extension pg_pltemplate pg_stat_all_tables pg_stat_user_tables pg_appendonly pg_extprotocol pg_prepared_statements pg_stat_archiver pg_stat_xact_all_tables pg_attrdef pg_exttable pg_prepared_xacts pg_stat_bgwriter pg_stat_xact_sys_tables pg_attribute pg_foreign_data_wrapper pg_proc pg_stat_database pg_stat_xact_user_functions pg_attribute_encoding pg_foreign_server pg_proc_callback pg_stat_database_conflicts pg_stat_xact_user_tables pg_authid pg_foreign_table pg_range pg_statio_all_indexes pg_tables pg_auth_members pg_group pg_replication_slots pg_statio_all_sequences pg_tablespace pg_auth_time_constraint pg_index pg_resgroup pg_statio_all_tables pg_timezone_abbrevs pg_available_extensions pg_indexes pg_resgroupcapability pg_statio_sys_indexes pg_timezone_names pg_available_extension_versions pg_inherits pg_resourcetype pg_statio_sys_sequences pg_toast. pg_bitmapindex. pg_language pg_resqueue pg_statio_sys_tables pg_trigger pg_cast pg_largeobject pg_resqueue_attributes pg_statio_user_indexes pg_ts_config pg_catalog. pg_largeobject_metadata pg_resqueuecapability pg_statio_user_sequences pg_ts_config_map pg_class pg_locks pg_resqueue_status pg_statio_user_tables pg_ts_dict pg_collation pg_matviews pg_rewrite pg_statistic pg_ts_parser pg_compression pg_max_external_files pg_roles pg_stat_last_operation pg_ts_template pg_constraint pg_namespace pg_rules pg_stat_last_shoperation pg_type pg_conversion pg_opclass pg_seclabel pg_stat_operations pg_type_encoding pg_cursors pg_operator pg_seclabels pg_stat_partition_operations pg_user pg_database pg_opfamily pg_settings pg_stat_replication pg_user_mapping pg_db_role_setting pg_partition pg_shadow pg_stat_resqueues pg_user_mappings pg_default_acl pg_partition_columns pg_shdepend pg_stats pg_views
查看对某个对象的权限
mydb=# select tablename,has_table_privilege(tablename,'select') has_select_priv from pg_tables where tablename like 'pg_%'; tablename | has_select_priv --------------------------+----------------- pg_authid | t pg_statistic | t pg_user_mapping | t pg_type | t
mydb=# select tablename,has_table_privilege(tablename,'select') select_priv,has_table_privilege(tablename,'INSERT') insert_priv,has_table_privilege(tablename,'UPDATE') update_priv from pg_tables where tablename like 'pg_%'; tablename | select_priv | insert_priv | update_priv --------------------------+-------------+-------------+------------- pg_authid | t | t | t pg_statistic | t | t | t pg_user_mapping | t | t | t pg_type | t | t | t pg_attribute | t | t | t pg_proc | t | t | t pg_class | t | t | t pg_attrdef | t | t | t pg_constraint | t | t | t pg_inherits | t | t | t pg_index | t | t | t pg_operator | t | t | t pg_opfamily | t | t | t pg_opclass | t | t | t pg_database | t | t | t pg_am | t | t | t pg_amop | t | t | t pg_amproc | t | t | t pg_language | t | t | t pg_largeobject_metadata | t | t | t pg_aggregate | t | t | t pg_rewrite | t | t | t pg_trigger | t | t | t pg_event_trigger | t | t | t pg_description | t | t | t pg_cast | t | t | t pg_enum | t | t | t pg_namespace | t | t | t pg_conversion | t | t | t pg_depend | t | t | t pg_db_role_setting | t | t | t pg_tablespace | t | t | t pg_pltemplate | t | t | t pg_auth_members | t | t | t pg_shdepend | t | t | t pg_shdescription | t | t | t pg_ts_config | t | t | t
PG系有点特殊，需要通过表函数来，LightDB 22.2将支持*_sys_privs,*_tab_privs,*_role_privs视图。
查看当前的事务ID
mydb=# select from txid_current(); -- 10之前 -- (1 row) mydb=# select from pg_current_xact_id(); -- 10及之后 -- (1 row)
pg 9.6有点像oracle 9i, 10有点像oracle 10g，在管理上有了较大的加强和重构。
information_schema
mysql兼容的information_schema schema，如下：
zjh@postgres=# select * from information_schema. information_schema.administrable_role_authorizations information_schema.foreign_data_wrapper_options information_schema.schemata information_schema.applicable_roles information_schema.foreign_data_wrappers information_schema.sequences information_schema.attributes information_schema.foreign_server_options information_schema.sql_features information_schema.character_sets information_schema.foreign_servers information_schema.sql_implementation_info information_schema.check_constraint_routine_usage information_schema.foreign_table_options information_schema.sql_parts information_schema.check_constraints information_schema.foreign_tables information_schema.sql_sizing information_schema.collation_character_set_applicability information_schema.information_schema_catalog_name information_schema.table_constraints information_schema.collations information_schema.key_column_usage information_schema.table_privileges information_schema.column_column_usage information_schema.parameters information_schema.tables information_schema.column_domain_usage information_schema._pg_foreign_data_wrappers information_schema.transforms information_schema.column_options information_schema._pg_foreign_servers information_schema.triggered_update_columns information_schema.column_privileges information_schema._pg_foreign_table_columns information_schema.triggers information_schema.columns information_schema._pg_foreign_tables information_schema.udt_privileges information_schema.column_udt_usage information_schema._pg_user_mappings information_schema.usage_privileges information_schema.constraint_column_usage information_schema.referential_constraints information_schema.user_defined_types information_schema.constraint_table_usage information_schema.role_column_grants information_schema.user_mapping_options information_schema.data_type_privileges information_schema.role_routine_grants information_schema.user_mappings information_schema.domain_constraints information_schema.role_table_grants information_schema.view_column_usage information_schema.domains information_schema.role_udt_grants information_schema.view_routine_usage information_schema.domain_udt_usage information_schema.role_usage_grants information_schema.views information_schema.element_types information_schema.routine_privileges information_schema.view_table_usage information_schema.enabled_roles information_schema.routines