PostgreSQL常用命令大全

1.啟動資料庫

[postgres@cjc-db-01 ~]$ pg_ctl -D /pg/data -l /pg/log/pg.log start

2.登入

[postgres@cjc-db-01 ~]$ psql
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb

3.退出登入

cjcdb=# \q

4.獲取幫助資訊

cjcdb=# \h
cjcdb=# \h create table

5.檢視\相關命令

cjcdb=# \?

6.資料庫版本

cjcdb=# select version();
cjcdb=# show server_version;

7.檢視有哪些資料庫

cjcdb=# \l
cjcdb=# \l+
cjcdb=# select oid,datname from pg_database;

8.檢視當前登入的資料庫

cjcdb=# select current_database();

9.切換資料庫

postgres=# \c cjcdb

10.切換使用者

cjcdb=# \c - chen

11.檢視當前使用者

cjcdb=# select user;
cjcdb=# select current_user;

12.檢視當前資料庫下所有表

cjcdb=> \d
cjcdb=> select tableowner,schemaname,tablename,tablespace from pg_tables where tableowner='cjc';

13.檢視當前資料庫下所有索引

cjcdb=# \di

14.檢視索引定義語句

cjcdb=# select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 't1';
cjcdb=# select pg_get_indexdef(上面語句查到的b.indexrelid);

15.檢視登入資訊

cjcdb=# \conninfo

16.檢視當前連線資訊

cjcdb=# select * from pg_stat_activity;

17.查詢表結構

cjcdb=> \d t1;
cjcdb=> \d+ t1;  
cjcdb=> select table_schema,table_name,column_name,data_type,character_maximum_length from information_schema.columns where table_name='t1';

18.檢視檢視

cjcdb=# \dv
cjcdb=# select * from pg_views where schemaname = 'public';
cjcdb=# select * from information_schema.views where table_schema = 'public';

19.檢視觸發器

cjcdb=# select * from information_schema.triggers;

20.檢視序列

cjcdb=# select * from information_schema.sequences where sequence_schema = 'public';

21.檢視約束

cjcdb=# select * from pg_constraint where contype = 'p'
cjcdb=# select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 't1';

22.檢視錶所對應的資料檔案路徑與大小

cjcdb=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';

23.檢視錶大小

cjcdb=# select pg_relation_size('t1');

24.檢視索引大小

cjcdb=# select pg_size_pretty(pg_relation_size('i_t1_id'));

25.檢視錶和索引總大小

cjcdb=# select pg_size_pretty(pg_total_relation_size('t1'));

26.檢視錶空間大小

cjcdb=# select pg_size_pretty(pg_tablespace_size('cjctbs'));

27.檢視角色資訊

cjcdb=# select rolname,rolsuper,rolcreatedb from pg_roles;

28.查詢使用者角色

cjcdb=# \dg
cjcdb=# \du

29.檢視使用者表許可權

cjcdb=# select * from information_schema.table_privileges where grantee='cjc';
cjcdb=# \dp

30.檢視錶空間

cjcdb=# \db

31.查詢資料檔案位置

cjcdb=# show data_directory;

32.查詢配置檔案位置

cjcdb=# show config_file;

33.檢視資料庫大小

cjcdb=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; 
cjcdb=# select pg_size_pretty(pg_database_size('cjcdb'));

格式化輸出

34.按列顯示

類似MySQL的\G

cjcdb=# \x
Expanded display is on.
cjcdb=# select * from t1;
...     
cjcdb=# \x
Expanded display is off.

35.顯示執行時間

cjcdb=# \timing on
cjcdb=# \timing of

36.執行shell命令

類似oracle的ho，MySQL的system命令

cjcdb=# \! pwd

37.格式化輸出\pset

加輸出邊框，類似MySQL

cjcdb=# select * from t1;
 id |    name    
----+------------
  1 | a         
  2 | aaa       
(2 rows)
cjcdb=# \pset border 2
Border style is 2.
cjcdb=# select * from t1;
+----+------------+
| id |    name    |
+----+------------+
|  1 | a          |
|  2 | aaa        |
+----+------------+
(2 rows)

取消邊框

cjcdb=# \pset border 0
Border style is 0.
cjcdb=# select * from t1;
id    name    
-- ----------
 1 a         
 2 aaa       
(2 rows)

38.調整分隔符為"|"

cjcdb=# \pset format unaligned
Output format is unaligned.
cjcdb=# select * from t1;
id|name
1|a         
2|aaa       
(2 rows)

39.調整分隔符為"Tab"

cjcdb=# \pset fieldsep '\t'
Field separator is "".
cjcdb=# select * from t1;
idname
1a         
2a

40.輸出結果到文字

類似oracle spool命令、MySQL tee命令。

cjcdb=# \o t1.txt
cjcdb=# select * from t1;
[postgres@cjc-db-01 ~]$ cat t1.txt 
idname
1a         
2aaa       
(2 rows)

41.顯示資訊

cjcdb=# \echo hahaha
hahaha

42.執行SQL指令碼

[postgres@cjc-db-01 ~]$ cat t1.sql 
select * from t1;
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb -f t1.sql 
Password for user cjc: 
 id |    name    
----+------------
  1 | a         
  2 | aaa       
(2 rows)

43.獲取快捷鍵實際執行的命令(引數-E)

例如，查詢有哪些資料庫

快捷鍵

檢視有哪些資料庫

cjcdb=# \l

實際執行的SQL

cjcdb=# \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   
-----------+----------+----------+-------------+-------------+-----------------------
 cjcdb     | cjc      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/cjc              +
           |          |          |             |             | cjc=CTc/cjc          +
           |          |          |             |             | chen=CTc/cjc
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | cjc=c/postgres
 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
(4 rows)

###chenjuchao 20230119 16:12###