本文介绍psql最常用的使用技巧，如历史命令和补全技巧、关闭自动提交功能、获得快捷命令实际的SQL，以便学习数据库的系统表等。
历史命令与补全功能
可以使用上下方向键把以前使用过的命令或SQL语句调出来，连续单击两次Tab键表示把命令补全或给出输入提示：
emp=> select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
emp=> d ←这里连续单击了两次Tab键
d dAo dE dFt dPi dT dc des df dm drds dv
dA dAp dF dL dPt dX dconfig det dg dn ds dx
dAc dC dFd dO dRp da dd deu di do dt dy
dAf dD dFp dP dRs db ddp dew dl dp du
emp=> d
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+----------+-------
public | countries | table | hr
public | departments | table | hr
public | departments_department_id_seq | sequence | hr
public | employees | table | hr
public | employees_employee_id_seq | sequence | hr
public | job_history | table | hr
public | jobs | table | hr
public | locations | table | hr
public | locations_location_id_seq | sequence | hr
public | regions | table | hr
public | regions_region_id_seq | sequence | hr
(11 rows)
emp=>
emp=> d emp ←这里连续单击了两次Tab键
emp_department_ix emp_job_ix emp_name_ix employees_employee_id_seq
emp_email_uk emp_manager_ix employees employees_pkey
emp=> d emp自动提交技巧
需要特别注意的是，在psql中事务是自动提交的，比如，执行完一条DELETE或UPDATE语句后，事务就会自动提交，如果不想让事务自动提交，方法有两种。
- 方法一：运行“begin;”命令，然后执行DML语句，最后再执行commit或rollback语句，示例如下。
postgres@linuxpg51:5432=#select * from haha;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+----------+------+------------+---------+--------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
(3 rows)
postgres@linuxpg51:5432=#begin;
BEGIN
postgres@linuxpg51:5432=#update haha set ename='ElonMusk' where empno=7369;
UPDATE 1
postgres@linuxpg51:5432=#select * from haha;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+----------+----------+------+------------+---------+--------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7369 | ElonMusk | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
(3 rows)
postgres@linuxpg51:5432=#rollback;
ROLLBACK
postgres@linuxpg51:5432=#select * from haha;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+----------+------+------------+---------+--------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
(3 rows)- 方法二：直接使用psql中的命令关闭自动提交功能。
postgres@linuxpg51:5432=#echo :AUTOCOMMIT
on
postgres@linuxpg51:5432=#set AUTOCOMMIT off
postgres@linuxpg51:5432=#echo :AUTOCOMMIT
off注意: 这个命令中的"AUTOCOMMIT"是大写的，不能使用小写，如果使用小写，虽不会报错，但会导致关闭自动提交的操作无效。
如何得到psql中快捷命令执行的实际SQL
在启动psql的命令行中加上“-E”参数，就可以把psql中各种以””开头的命令执行的实际SQL语句打印出来，示例如下：
[pgsql@linuxpg51:/home/pgsql]$psql -E postgres
Password for user postgres:
psql (12.2)
Type "help" for help.
postgres@linuxpg51:5432=#d
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname 'pg_catalog'
AND n.nspname 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+--------------------+-------+----------
public | bonus | table | postgres
public | company | table | postgres
public | dept | table | postgres
public | document_template | table | postgres
public | emp | table | postgres
public | haha | table | postgres
public | pg_stat_statements | view | postgres
public | salgrade | table | postgres
(8 rows)
postgres@linuxpg51:5432=#d emp
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.re服务器托管网lnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(emp)$' COLLATE pg_catalog.default
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **服务器托管网********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = '16586';
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation t.typcollation) AS attcollation,
a.attidentity,
a.attgenerated
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16586' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************
********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = '16586' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
**************************
********* QUERY **********
SELECT true as sametable, conname,
pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
conrelid::pg_catalog.regclass AS ontable
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16586' AND r.contype = 'f'
AND conparentid = 0
ORDER BY conname
**************************
********* QUERY **********
SELECT conname, conrelid::pg_catalog.regclass AS ontable,
pg_catalog.pg_get_constraintdef(oid, true) AS condef
FROM pg_catalog.pg_constraint c
WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('16586')
UNION ALL VALUES ('16586'::pg_catalog.regclass))
AND contype = 'f' AND conparentid = 0
ORDER BY conname;
**************************
********* QUERY **********
SELECT pol.polname, pol.polpermissive,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '16586' ORDER BY 1;
**************************
********* QUERY **********
SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname,
(SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')
FROM pg_catalog.unnest(stxkeys) s(attnum)
JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND
a.attnum = s.attnum AND NOT attisdropped)) AS columns,
'd' = any(stxkind) AS ndist_enabled,
'f' = any(stxkind) AS deps_enabled,
'm' = any(stxkind) AS mcv_enabled
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '16586'
ORDER BY 1;
**************************
********* QUERY **********
SELECT pubname
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid
WHERE pr.prrelid = '16586'
UNION ALL
SELECT pubname
FROM pg_catalog.pg_publication p
WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('16586')
ORDER BY 1;
**************************
********* QUERY **********
SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled, t.tgisinternal
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = '16586' AND (NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D')
OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid
AND refclassid = 'pg_catalog.pg_trigger'::pg_catalog.regclass))
ORDER BY 1;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '16586' AND c.relkind != 'p' ORDER BY inhseqno;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid), c.relkind FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '16586' ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
**************************
Table "public.emp"
Column | Type | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
empno | numeric | | not null |
ename | character varying(10) | | |
job | character varying(9) | | |
mgr | numeric | | |
hiredate | date | | |
sal | numeric(7,2) | | |
comm | numeric(7,2) | | |
deptno | numeric(2,0) | | |
Indexes:
"pk_emp" PRIMARY KEY, btree (empno)
Foreign-key constraints:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)如果在已运行的psql中显示了某个命令实际执行的SQL语句后又想关闭此功能，该怎么办？这时可以使用”set ECHO_HIDDEN on|off”命令，示例如下：
emp=> set ECHO_HIDDEN on
emp=> du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
emp=> set ECHO_HIDDEN off
emp=> du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
emp=>通过分析这个方法输出的SQL语句，可以让我们快速学习PostgreSQL的系统表原理。
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
相关推荐: element ui vue sass实现帮助中心功能
使用：element ui、vue、sass 关键组件：el-autocomplete、el-scrollbar、el-tree、el-backtop 代码 帮助中心 搜索 {{ item.name }} {{ item.descr }} {{ infoNam…
