PostgreSQL 命令列工具 psql 使用

– 11 分鐘閱讀

在資料函式庫開發和管理中，熟練使用命令列工具至關重要。本文將引導你瞭解 PostgreSQL 的命令列工具 psql，從連線到資料函式庫、執行查詢到格式化輸出結果，逐步提升你的資料函式庫操作效率。psql 提供一個互動式介面，讓你可以直接輸入 SQL 指令並檢視結果，同時也支援許多方便的元命令來管理資料函式庫和控制輸出。

啟動與組態 Windows 命令提示字元

命令提示字元是一個名為 cmd.exe 的可執行檔。要啟動它，請選擇「開始」▶「Windows 系統」▶「命令提示字元」，或在搜尋列中輸入 cmd。當應用程式開啟時，您應該會看到一個黑色背景的視窗，顯示版本和版權資訊，以及顯示目前目錄的提示字元。在我的 Windows 10 系統上，命令提示字元開啟時預設為我的使用者目錄，並顯示 C:\Users\adeba>，如圖 18-3 所示。

設定命令提示字元

這一行被稱為提示字元，它顯示目前的工作目錄。對我來說，這是我的 C: 磁碟機，通常是 Windows 系統上的主要硬碟，以及該磁碟機上的 \Users\adeba 目錄。大於符號 > 表示您輸入命令的區域。

快速存取命令提示字元

為了快速存取命令提示字元，您可以將其固定到 Windows 工作列。當命令提示字元正在執行時，右鍵點選其在工作列上的圖示，然後點選「固定到工作列」。

圖 18-3：我在 Windows 10 中的命令提示字元

您可以透過點選命令提示字元視窗左上角的圖示並選擇「內容」，來自訂字型和顏色，以及存取其他設定。為了使命令提示字元更適合查詢輸出的顯示，我建議將視窗大小（在「組態」標籤下）設定為寬度至少 80 和高度 25。對於字型，官方 PostgreSQL 檔案建議使用 Lucida Console 以正確顯示所有字元。

在 Windows 命令提示字元中輸入指令

現在您已經準備好在命令提示字元中輸入指令。在提示字元下輸入 help，然後按下鍵盤上的 ENTER 鍵，即可看到可用的 Windows 系統指令清單。您可以透過在 help 後面加上指令名稱來檢視有關該指令的資訊。例如，輸入 help time 即可顯示有關使用 time 指令來設定或檢視系統時間的資訊。

探索 Windows 命令

探索命令提示字元的全部功能是一個龐大的主題，超出了本文的範圍。但是，我鼓勵您嘗試表 18-1 中的一些指令，這些是常用的指令，但並非本章練習的必要指令。

表 18-1：有用的 Windows 指令

指令功能範例動作
cd變更目錄cd C:\my-stuff切換到 C: 磁碟機上的 my-stuff 目錄
copy複製檔案copy C:\my-stuff\song.mp3 C:\Music\song_favorite.mp3將 song.mp3 從 my-stuff 複製到 Music 目錄下的 song_favorite.mp3
del刪除del *.jpg刪除目前目錄下所有副檔名為 .jpg 的檔案（星號萬用字元）
dir列出目錄內容dir /p一次一頁顯示目錄內容（使用 /p 選項）
findstr在文字檔中尋找符合正規表示式的字串findstr "peach" *.txt在目前目錄下的所有 .txt 檔案中搜尋文字「peach」
mkdir建立新目錄mkdir C:\my-stuff\Salad在 my-stuff 目錄下建立 Salad 目錄
move移動檔案move C:\my-stuff\song.mp3 C:\Music\將 song.mp3 移到 C:\Music 目錄

當您的命令提示字元開啟並組態完成後，您就可以繼續進行下一節「使用 psql」。

macOS psql 設定

在 macOS 上，您將在 Terminal 中執行 psql，Terminal 是透過多個 shell 程式（如 bash 或 zsh）提供對系統命令列存取的應用程式。Unix 或 Linux 系統（包括 macOS）上的 shell 程式不僅提供使用者輸入指令的命令提示字元，還提供自己的程式語言，用於自動化任務。例如，您可以使用 bash 指令編寫程式來登入遠端電腦、傳輸檔案並登出。

啟動與組態 macOS 終端機

透過導覽至「應用程式」▶「工具程式」▶「終端機」來啟動終端機。當它開啟時，您應該會看到一個視窗，顯示您上次登入的日期和時間，後面跟著一個提示字元，其中包含您的電腦名稱、目前工作目錄和使用者名稱。在我的 Mac 上，它被設定為 bash shell，提示字元顯示為 ad:~ anthony$，並以美元符號（$）結尾，如圖 18-4 所示。

波浪符號（~）代表系統的主目錄，即 /Users/anthony。終端機不會顯示完整的目錄路徑，但您可以隨時透過輸入 pwd 指令（print working directory 的縮寫）並按下鍵盤上的 ENTER 鍵來檢視該資訊。美元符號後的區域是用於輸入指令的區域。

如果您的 Mac 設定為其他 shell（如 zsh），您的提示字元可能會有所不同。例如，使用 zsh 時，提示字元以百分比符號結尾。您使用的特定 shell 對這些練習沒有影響。

圖 18-4：macOS 中的終端機命令列

快速存取終端機

為了快速存取終端機，請將其新增至您的 macOS Dock。當終端機正在執行時，右鍵點選其圖示並選擇「選項」▶「保留在 Dock 中」。

如果您從未使用過終端機，其預設的黑白配色方案可能看起來很單調。您可以透過選擇「終端機」▶「偏好設定」來變更字型、顏色和其他設定。為了使終端機變大以更好地顯示查詢輸出，我建議將視窗大小（在「組態檔」的「視窗」標籤下）設定為寬度至少 80 列和高度 25 行。我偏好的字型（在「文字」標籤下）是 Monaco 14，但您可以嘗試找到您喜歡的字型。

探索終端機與相關指令

探索終端機和相關指令的全部功能是一個龐大的主題，超出了本文的範圍，但請花一些時間嘗試幾個指令。表 18-2 列出了一些有用的常用指令，這些指令並非本章練習的必要指令。輸入 man（manual 的縮寫）後跟指令名稱，即可檢視該指令的手冊頁面，以瞭解更多關於該指令的資訊。

使用終端機與psql的基本操作

在進行資料函式庫操作時，熟悉終端機（命令列介面）的使用以及psql的基本指令是至關重要的。本篇文章將介紹如何在不同作業系統中使用終端機，以及如何使用psql與PostgreSQL資料函式庫互動。

終端機的基本使用

在進行資料函式倉管理工作時，終端機（或命令提示字元）是一個強大的工具。無論是在Windows、macOS還是Linux系統上，終端機都提供了一個直接與作業系統互動的介面。

常用終端機指令

以下是一些在終端機中常用的指令：

  • 變更目錄：cd /path/to/directory
  • 複製檔案：cp source_file target_file
  • 尋找文字：grep 'search_text' file_name
  • 列出目錄內容：ls -al
  • 建立新目錄：mkdir new_directory
  • 移動或重新命名檔案：mv source_file target_file
  • 刪除檔案：rm file_to_delete

在不同作業系統中使用終端機

  1. Windows：使用命令提示字元（Command Prompt）或PowerShell。
  2. macOS：使用終端機（Terminal）應用程式。
  3. Linux：使用各發行版提供的終端機應用程式，如Ubuntu上的GNOME Terminal。

使用psql與PostgreSQL互動

psql是PostgreSQL的命令列客戶端工具，允許使用者直接與PostgreSQL資料函式庫互動。

連線到資料函式庫

要連線到一個PostgreSQL資料函式庫，可以使用以下指令：

psql -d database_name -U user_name

例如：

psql -d analysis -U postgres

如果需要連線到遠端伺服器上的資料函式庫，可以加入-h引數指定主機名：

psql -d analysis -U postgres -h example.com

在psql中取得幫助或離開

在psql提示字元下，可以使用以下meta-commands取得幫助或執行其他操作：

  • \?：列出所有可用的meta-commands。
  • \h：提供特定SQL指令的詳細幫助，例如\h INSERT。
  • \q：離開psql。

切換資料函式庫連線

可以使用\c meta-command切換到不同的資料函式庫：

\c database_name

psql中的Meta-Commands詳解

在psql中，除了執行SQL查詢外，還可以使用meta-commands來執行諸如列出資料函式庫物件、調整設定等操作。

內容解密：

Meta-commands是用於控制psql行為或檢索有關資料函式庫的資訊的特殊指令。它們總是以反斜線（\）開頭。瞭解這些指令對於高效地使用psql至關重要。

PostgreSQL 的 psql 工具使用

PostgreSQL 是一種強大的開源關聯式資料函式倉管理系統，而 psql 是其對應的命令列工具，能夠讓使用者與資料函式庫進行互動。本文將介紹 psql 的基本使用方法，包括連線資料函式庫、執行 SQL 查詢、設定密碼檔等。

連線資料函式庫

當你開啟 psql 時，預設會連線到與你的使用者名稱相同的資料函式庫。若要連線到其他資料函式庫，可以使用 \c 或 \connect 命令。例如：

\c test

此命令會將你連線到名為 “test” 的資料函式庫。

若要以不同的使用者身份連線資料函式庫，可以在資料函式庫名稱後面加上使用者名稱：

\c test anthony

這樣就會以 “anthony” 使用者的身份連線到 “test” 資料函式庫。

移除測試資料函式庫

若要移除先前建立的測試資料函式庫，首先需要斷開與該資料函式庫的連線，並切換到其他資料函式庫，例如 “analysis”：

\c analysis
DROP DATABASE test;

設定密碼檔

為了避免每次啟動 psql 時都需要輸入密碼，可以建立一個密碼檔來儲存資料函式庫連線資訊。在 Windows 上，這個檔案需要命名為 pgpass.conf，並存放在 C:\USERS\YourUsername\AppData\Roaming\postgresql\ 目錄下。在 macOS 和 Linux 上，則需要命名為 .pgpass，並存放在使用者主目錄下。

檔案的內容格式如下：

hostname:port:database:username:password

例如：

localhost:5432:analysis:postgres:password

你也可以使用星號 (*) 作為萬用字元來匹配多個資料函式庫或使用者。

執行 SQL 查詢

在 psql 中，可以直接輸入 SQL 查詢陳述式來執行。例如：

SELECT county_name FROM us_counties_pop_est_2019 ORDER BY county_name LIMIT 3;

此查詢會從 us_counties_pop_est_2019 表格中選取前三個縣名，並按照縣名字母順序排序。

輸入多行查詢

若查詢陳述式較長，可以分多行輸入。psql 會在你輸入完畢並加上分號後才執行查詢。例如：

SELECT county_name
FROM us_counties_pop_est_2019
ORDER BY county_name
LIMIT 3;

檢查未閉合的括號

psql 會在提示符中顯示未閉合的括號。例如：

CREATE TABLE wineries (
id bigint,
winery_name text
);

在輸入 CREATE TABLE 陳述式時，psql 會在提示符中顯示未閉合的括號，直到你輸入閉合括號為止。

編輯查詢陳述式

可以使用 \e 或 \edit 命令來編輯最近執行的查詢陳述式。psql 會開啟一個文字編輯器，讓你編輯查詢陳述式。編輯完成後，儲存並離開編輯器，psql 就會執行修改後的查詢陳述式。

在 Windows 上，psql 預設會開啟 Notepad 編輯器。在 macOS 和 Linux 上，則預設會開啟 vim 編輯器。

內容解密：
  1. 連線資料函式庫：使用 \c 命令可以連線到指定的資料函式庫，並可選擇以特定使用者身份進行連線。
  2. 移除測試資料函式庫：先斷開與測試資料函式庫的連線，然後使用 DROP DATABASE 命令刪除該資料函式庫。
  3. 設定密碼檔：建立密碼檔（如 pgpass.conf 或 .pgpass）來儲存資料函式庫連線資訊，以避免每次輸入密碼。
  4. 執行 SQL 查詢：直接在 psql 中輸入 SQL 查詢陳述式即可執行，可單行或多行輸入。
  5. 檢查未閉合的括號：psql 會在提示符中顯示未閉合的括號，幫助使用者檢查語法錯誤。
  6. 編輯查詢陳述式：使用 \e 或 \edit 命令可編輯最近執行的查詢陳述式，並重新執行修改後的陳述式。

使用psql的進階技巧：查詢結果的導航與格式化

在上一節中，我們學習瞭如何使用psql執行查詢並檢視結果。然而，當查詢傳回大量資料時，如何有效地導航和格式化結果就變得非常重要。本文將介紹如何使用psql的元命令（meta-command）來控制查詢結果的顯示方式。

設定結果的分頁顯示

當查詢傳回的結果超過一屏時，psql預設會使用分頁顯示。這意味著它會先顯示第一屏的結果，然後你可以透過滾動來檢視剩下的結果。例如，執行以下查詢：

SELECT county_name FROM us_counties_pop_est_2019 ORDER BY county_name;

你會看到類別似於下面的輸出：

county_name
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
Abbeville County
Acadia Parish
Accomack County
Ada County
Adair County
Adair County
-- More --

內容解密：

  1. SELECT county_name FROM us_counties_pop_est_2019 ORDER BY county_name;：這條SQL查詢陳述式從us_counties_pop_est_2019表中選擇county_name欄位，並按照縣名進行排序。
  2. -- More --：這個提示表示還有更多的結果可以檢視，你可以按ENTER鍵來滾動檢視剩下的結果。
  3. 在Windows系統上，會顯示-- More --，而在macOS和Linux上，則會顯示一個冒號（:）。

如果你希望一次性顯示所有結果，而不需要滾動，可以使用\pset pager元命令來關閉分頁功能。執行後，你會看到如下訊息：

Pager usage is off.

現在，再次執行之前的查詢，你會看到所有結果一次性顯示出來，而不需要滾動。

格式化結果網格

除了控制分頁外，你還可以使用\pset元命令來格式化結果的顯示。以下是一些常用的選項：

  • border int：用於設定結果網格的邊框樣式。例如，\pset border 2會在所有儲存格周圍顯示邊框。
  • format unaligned：將結果以分隔符號分隔的行顯示，而不是以欄位顯示。你可以使用\pset fieldsep ','來設定分隔符號為逗號。
  • footer：用於切換結果頁尾的顯示，頁尾通常包含結果的列數。
  • null：用於設定如何顯示NULL值。預設情況下，NULL值顯示為空白。你可以使用\pset null '(null)'來將NULL值顯示為(null)。

檢視展開的結果

有時，將結果以垂直列表的形式顯示會更方便，尤其是當欄位數量很多時。你可以使用\x元命令來切換到展開檢視模式。

例如，執行以下查詢：

SELECT * FROM grades ORDER BY student_id, course_id;

在正常檢視模式下，你會看到類別似於下面的輸出：

student_id | course_id | course        | grade
---
-
---
-
---
+
---
-
---
-
---
+
---
-
---
-
---
-
---
+
---
-
---
1          | 1         | Biology 2     | C
1          | 2         | English 11B   | D
1          | 3         | World History | C

內容解密：

  1. SELECT * FROM grades ORDER BY student_id, course_id;：這條SQL查詢陳述式從grades表中選擇所有欄位，並按照student_id和course_id進行排序。
  2. 結果以表格形式呈現，每一行代表一個學生的成績記錄。

執行\x後，再次執行相同的查詢，你會看到展開的結果，如下所示：

-[ RECORD 1 ]
---
-
---
-
---
-
---
--
student_id | 1
course_id  | 1
course     | Biology 2
grade      | C
-[ RECORD 2 ]
---
-
---
-
---
-
---
--
student_id | 1
course_id  | 2
course     | English 11B
grade      | D

內容解密：

  1. **`-[ RECORD n ]

–`**：這表示第n條記錄的開始。 2. 每個欄位的值以鍵值對的形式顯示，使得檢視和比較變得更加容易。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。