PostgreSQL快速入門：psql工具的使用

<p style="padding-bottom:0px;background-color:transparent;margin-top:0px;word-wrap:break-word;white-space:pre;margin-bottom:0px;color:windowtext;font-size:6pt;vertical-align:baseline;padding-top:0px;-webkit-user-select:text;-webkit-user-drag:none;-webkit-tap-highlight-color:transparent;" class="Paragraph SCX250954401" xml:lang="ZH-CN"></p>

postgres=# CREATE DATABASE osdba; 
CREATE DATABASE 
postgres-# \l 
                                  List of databases 
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges    
-----------+----------+----------+-------------+-------------+----------------------- 
 osdba     | 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 
(4 rows)

3.訪問osdba數據庫 

-bash-4.1$ psql osdba 
psql (8.4.20) 
Type "help" for help.

4.在數據庫osdba中創建表t 

osdba=# create table t(id int primary key,name varchar(40)); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" 
CREATE TABLE

5.查看osdb數據庫中的表 

osdba=# \d 
        List of relations 
 Schema | Name | Type  |  Owner    
--------+------+-------+---------- 
 public | t    | table | postgres 
(1 row)

6.創建數據庫testdb后，并連接到testdb數據庫 

osdba=# CREATE DATABASE testdb; 
CREATE DATABASE 
osdba=# \c testdb 
psql (8.4.20) 
You are now connected to database "testdb". 
testdb=#

三、psql的常用命令 

1.\d命令-查看當前數據庫中的所有表 

osdba-# \d 
        List of relations 
 Schema | Name | Type  |  Owner    
--------+------+-------+---------- 
 public | t    | table | postgres 
(1 row)

2.\d命令-跟一個表命，查看這個表的結構定義 

osdba-# \d t 
              Table "public.t" 
 Column |         Type          | Modifiers  
--------+-----------------------+----------- 
 id     | integer               | not null 
 name   | character varying(40) |  
Indexes: 
    "t_pkey" PRIMARY KEY, btree (id)

3.\d命令-可以查看表格t的索引信息 

osdba-# \d t_pkey 
Index "public.t_pkey" 
 Column |  Type    
--------+--------- 
 id     | integer 
primary key, btree, for table "public.t"

4.\d命令-跟通配符如*或？ 

osdba-# \d t* 
              Table "public.t" 
 Column |         Type          | Modifiers  
--------+-----------------------+----------- 
 id     | integer               | not null 
 name   | character varying(40) |  
Indexes: 
    "t_pkey" PRIMARY KEY, btree (id) 

Index "public.t_pkey" 
 Column |  Type    
--------+--------- 
 id     | integer 
primary key, btree, for table "public.t"

5.\d+命令，顯示比\d命令更加詳細的信息，顯示與表列關聯的注釋 

osdba-# \d+ 
                    List of relations 
 Schema | Name | Type  |  Owner   |  Size   | Description  
--------+------+-------+----------+---------+------------- 
 public | t    | table | postgres | 0 bytes |  
(1 row)

6.匹配不同對象類型的\d命令，如\dt-只想顯示匹配的表；\di-只想顯示索引；\ds-只顯示序列；\dv-只顯示視圖；\df-只顯示函數等... 

osdba-# \dt t* 
        List of relations 
 Schema | Name | Type  |  Owner    
--------+------+-------+---------- 
 public | t    | table | postgres

7.\dn命令-列出所有的schema 

osdba-# \dn 
        List of schemas 
        Name        |  Owner    
--------------------+---------- 
 information_schema | postgres 
 pg_catalog         | postgres 
 pg_toast_temp_1    | postgres 
 public             | postgres 
(5 rows)

8.\db命令-顯示所有表空間 

osdba-# \db 
       List of tablespaces 
    Name    |  Owner   | Location  
------------+----------+---------- 
 pg_default | postgres |  
 pg_global  | postgres |  
(2 rows)

9.\dg-列出數據庫中所有角色和用戶 

osdba-# \dg 
            List of roles 
 Role name | Attributes  | Member of  
-----------+-------------+----------- 
 postgres  | Superuser   | {} 
           : Create role    
           : Create DB

10.\dp-顯示表的權限分配情況 

osdba-# \dp 
                          Access privileges 
 Schema | Name | Type  | Access privileges | Column access privileges  
--------+------+-------+-------------------+-------------------------- 
 public | t    | table |                   |  
(1 row)

四、指定字符集編譯的命令 

1.\encoding gbk-設置客戶端的字符編碼為gbk 

五、\pset命令 

1.\pset命令-用于設置輸出的格式，\pset border 0/1/2：輸出內容無邊框、只有內邊框、內外都有邊框 

osdba-# \pset border 0 
Border style is 0. 
osdba-# \dp 
                     Access privileges 
Schema Name Type  Access privileges Column access privileges 
------ ---- ----- ----------------- ------------------------ 
public t    table                    
(1 row) 

osdba-# \pset border 1 
Border style is 1. 
osdba-# \dp 
                          Access privileges 
 Schema | Name | Type  | Access privileges | Column access privileges  
--------+------+-------+-------------------+-------------------------- 
 public | t    | table |                   |  
(1 row) 

osdba-# \pset border 2 
Border style is 2. 
osdba-# \dp 
                           Access privileges 
+--------+------+-------+-------------------+--------------------------+ 
| Schema | Name | Type  | Access privileges | Column access privileges | 
+--------+------+-------+-------------------+--------------------------+ 
| public | t    | table |                   |                          | 
+--------+------+-------+-------------------+--------------------------+ 
(1 row)

2.\x命令-可以把表中的每一行的每列數據都拆分為單行展示，如果有一行數據有太多的拆行，顯示不下，就可以使用這個命令 

osdba-# \x 
Expanded display is on. 
osdba-# \dp 
Access privileges 
+-[ RECORD 1 ]-------------+--------+ 
| Schema                   | public | 
| Name                     | t      | 
| Type                     | table  | 
| Access privileges        |        | 
| Column access privileges |        | 
+--------------------------+--------+ 

osdba-# \x 
Expanded display is off. 
osdba-# \dp 
                           Access privileges 
+--------+------+-------+-------------------+--------------------------+ 
| Schema | Name | Type  | Access privileges | Column access privileges | 
+--------+------+-------+-------------------+--------------------------+ 
| public | t    | table |                   |                          | 
+--------+------+-------+-------------------+--------------------------+ 
(1 row)

五、psql使用技巧 

1.使用上下鍵把過去使用過的命令或者SQL語句調出來，連續兩個tab鍵表示補全或給出提示輸入 

osdba-# \d 
\d    \dc   \dD   \dew  \dFd  \dg   \dn   \ds   \dT    
\da   \dC   \des  \df   \dFp  \di   \do   \dS   \du    
\db   \dd   \deu  \dF   \dFt  \dl   \dp   \dt   \dv

2.psql加上-E參數，可以把psql中各種以"\"開頭的命令執行的實際SQL打印出來 

-bash-4.1$ psql -E postgres 
psql (8.4.20) 
Type "help" for help. 

postgres=# \d 
********* QUERY ********** 
SELECT n.nspname as "Schema", 
  c.relname as "Name", 
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' 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','v','S','') 
      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; 
************************** 

No relations found

如果你在使用之后，想立即關閉 

postgres=# \set ECHO_HIDDEN off 
postgres=# \d 
No relations found.