PG如何在psql命令中显示快捷命令真实执行的SQL语句
👉 本文共约261个字，系统预计阅读时间或需1分钟。
如何查看已有权限，如何查看某个schema下所有对象的权限等。 可以使用psql提供的一些快捷命令。
1 | \dp+ usera.* |
如果想知道这些快捷命令后面用了什么SQL，可以开启-E选项
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | C:\Users\lhrxxt>psql -U postgres -h 192.168.66.35 -d postgres -p15432 -E Password for user postgres: psql (11.6, server 11.11) Type "help" for help. postgres=# \l ********* QUERY ********** SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1; ************************** List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- lhrpg | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | lhrpgdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows) |
也可以在psql中执行\set ECHO_HIDDEN ON
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | postgres=# \set ECHO_HIDDEN on postgres=# \l ********* QUERY ********** SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1; ************************** List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- db1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows) postgres=# \set AUTOCOMMIT = 'on' COMP_KEYWORD_CASE = 'preserve-upper' DBNAME = 'postgres' ECHO = 'none' ECHO_HIDDEN = 'On' ENCODING = 'GBK' ERROR = 'false' FETCH_COUNT = '0' HIDE_TABLEAM = 'off' HISTCONTROL = 'none' HISTSIZE = '500' HOST = '192.168.66.35' IGNOREEOF = '0' LASTOID = '0' LAST_ERROR_MESSAGE = 'relation "nage_age" does not exist' LAST_ERROR_SQLSTATE = '42P01' ON_ERROR_ROLLBACK = 'off' ON_ERROR_STOP = 'off' PORT = '15432' PROMPT1 = '%/%R%x%# ' PROMPT2 = '%/%R%x%# ' PROMPT3 = '>> ' QUIET = 'off' ROW_COUNT = '1' SERVER_VERSION_NAME = '13.2' SERVER_VERSION_NUM = '130002' SHOW_CONTEXT = 'errors' SINGLELINE = 'off' SINGLESTEP = 'off' SQLSTATE = '00000' USER = 'postgres' VERBOSITY = 'default' VERSION = 'PostgreSQL 13.3, compiled by Visual C++ build 1914, 64-bit' VERSION_NAME = '13.3' VERSION_NUM = '130003' postgres=# \set ECHO_HIDDEN OFF postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- db1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | lhrdb1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows) |
参考：https://www.xmmup.com/%e5%b8%b8%e7%94%a8%e7%9a%84psql%e5%91%bd%e4%bb%a4.html
