(本文章原始記錄於 2010 年 5 月 24 日)

******* psql Client 端資料庫操作介面 *******

  1. psql 指令:
    psql 是 Command Line 形式的 Client 端 PostgreSQL 操作介面。
    一般來說,資料庫的資料存取大部份都是各程式語言透過對應的連結用驅動程式來操作,
    而 psql 可以透過下 SQL 指令的方式更快速直接執行各種操作,包括新增刪除資料庫使用者;除了使用 psql 外,也可以透過 GUI 介面 pgAdmin3 來進行各式操作。

    進入 psql 操作介面的使用語法如下:
    C:\> psql [OPTION] … [DBNAME [USERNAME]]

    若無指定 -U 或 -d,則要將 DBNAME 及 USER 放在指令最後,並以 DBNAME→USER 的順序輸入,遇到第一個無指定 option 的參數會當成 DBNAME,遇到第二個無指定 option 的參數會當成 USER,因此若省略第二個參數,psql 會指定以系統使用者的名稱(default)登入。

    常用 option 如下:

    -d DBNAME 設定要連結到哪一個資料庫
    === 不直接進入 psql 介面,執行完程式後還是在外面( shell ) ===
    -c COMMAND 直接下 SQL 指令,並將執行結果顯示在外面( stdout ),
    接受如 \h、\? 之類的特殊指令
    -f FILENAME 從檔案讀入 SQL 指令後執行,並將結果顯示在外面( stdout ),
    同 -c 選項,接受如 \h、\? 之類的特殊指令
    -l 列出 Cluster 內的所有資料庫一覽到外面( stdout )
    -o FILENAME 將上述原本要顯示到螢幕( stdout )上的資料存到指定的檔案中,
    但不接受內建的特殊指令如 \c、\h、\? 等之輸出資料( \l 可以);
    輸出後會完全覆寫取代檔案中原本的內容
    === 以下為連線選項,表示要連線到哪一個 PostgreSQL Cluster 上 ===
    -h HOST 指定主機名稱
    -p PORT 指定主機 port
    -U USER 指定由哪位使用者登入
    -W 強制使用者輸入密碼作確認,若 initdb 時指定 -A password 的話,則不管是否有此選項都會要求使用者輸入密碼

    範例:
    C:\> psql template1 tester
    以 tester 帳號登入 template1 資料庫,若須密碼確認,則會出現提示,要求輸入密碼。

    C:\> psql -f C:/postgresql8.4.31/sqls/query.sql
    -o C:/postgresql8.4.31/logs/dblist.txt test norm
    以 norm 使用者登入 test 資料庫,執行 C:/postgresql8.4.31/sqls/query.sql 檔案內的 SQL 指令之後,將執行結果輸出儲存到 C:/postgresql8.4.31/logs/dblist.txt 檔案中。

    C:\> psql -p 5432 -U admin -d postgres
    以 admin 使用者登入到 port 為 5432 的 PostgreSQL Cluster 中的 postgres 資料庫(未指定主機則是登入預設的 localhost)

    關於 psql 的用法可以參考 C:\> psql --help

  2. psql 的 prompt 字元意義為:<資料庫名稱><[=|-]><權限>
    例如:

    test=> 表示登入 test 資料庫的一般使用者
    test-> 表示登入 test 資料庫的一般使用者,SQL 尚未下達完成(以 ; 結束)
    postgres=# 表示登入 postgres 資料庫的 superuser
  3. psql Command Line 介面 SQL 指令操作:
    進入 psql 後可以下 SQL 指令進行各項操作,例如:

    建立資料庫 CREATE DATABASE <dbname>
    建立資料表 CREATE TABLE <tablename>(COLUMN …)
    刪除資料庫 DROP DATABASE <dbname>
    刪除資料表 DROP TABLE <tablename>
    修改資料表 ALTER TABLE <tablename> …
    修改使用者 ALTER USER <user> WITH …
    加入資料 INSERT INTO <tablename>(COLUMN …) VALUES(…)
    刪除資料 DELETE FROM <tablename> WHERE …
    修改資料 UPDATE <tablename> SET COLUMN=’…’,… WHERE …

    關於 psql 介面的 SQL 指令可以使用 \h [SQL COMMAND] 查詢,
    例如 \h ALTER USER 可以查詢 ALTER USER 的用法,
    \h 不加任何參數則是列出所有可用的 SQL 關鍵字。

  4. psql 內建指令
    有些動作在 psql 中並非透過 SQL 來達成,而是透過內建的指令來執行,
    例如 MySQL 用 SHOW DATABASES 來列出所有的資料庫,而 psql 則是用 \l 指令。

    常用的指令如下:( d = display,S = 展示系統物件,+ = 額外更細部的資料)

    \c[onnect] [DBNAME|- USER|- HOST|- PORT|-] 以 USER 帳號登入 HOST 主機 PORT 埠號的 DBNAME 資料庫中,以一定的順序輸入,若想保持目前的值而不填資料,則以「-」號代替
    \l[+] 列出目前 Cluster 中已建立的所有資料庫
    \du[+] 列出目前 Cluster 中已建立的所有使用者
    \d[S[+]] 列出目前資料庫中的所有資料表等資訊,
    加上 S+ 為更完整資料
    \d[S[+]] TBLNAME 列出 TBLNAME 資料表中的所有欄位資料,
    加上 S+ 為更完整資料
    \q 跳出 psql 回到外部 shell 中
    \cd [DIR] 變更目前所在的目錄,若不指定 DIR 參數則是跳到根目錄 C: 或 D:,若是指定 DIR 參數則會 Change Directory 到該目錄下;但是用 \q 跳出 psql 後還是會位於原本的目錄中
    \! [COMMAND] 執行外部目錄中的程式,例如 \! dir 為列出目前目錄中的所有檔案
    \encoding [ENCODING] 顯示目前資料庫的編碼。若指定 ENCODING 參數則是暫時變更目前編碼
    \password [USERNAME] 變更目前使用者的密碼。若指定 USERNAME 參數則是變更該使用者密碼

    範例:
    postgres=# \c – root
    變更成以 root 帳號登入目前的資料庫 postgres

    postgres=# \c template1 – – 5432
    以目前的帳號登入目前主機 port 為 5432 的 Cluster 中的 template1 資料庫

    postgres=# \cd C:/postgresql8.4.31/logs
    將外部目錄變更到 C:/postgresql8.4.31/logs 下

    postgres=# \! dir/w
    執行外部 shell 命令 dir/w 列出目前目錄下的所有檔案及目錄

    postgres=# \password tester
    變更使用者 tester 的密碼

    關於 psql 介面的內建指令可以使用 \? 查詢