PostgreSQL 速查、備忘手冊 | PostgreSQL Quick Find and Tutorial

天靖居士發表於2018-04-17
SQLUI

PostgreSQL 速查、備忘手冊

作者：汪嘉霖
這是一個你可能需要的一個備忘手冊，此手冊方便你快速查詢到你需要的常見功能。有時也有一些曾經被使用過的高階功能。如無特殊說明，此手冊僅適用於 Linux 下（但是許多也都可以在 Windows 下的 psql 命令窗執行），部分功能可能需要你的軟體版本不能太低。
歡迎新增你認為有價值的備忘！前往我們的GitHub

切換到 postgres 賬戶

$ sudo -i -u postgres # 你可能需要在 sudoers list裡面，也就是你可以執行

進入預設 psql 命令列（預設 postgres 資料庫）

此時你的命令前面變成 "postgres=# " 的字樣，說明你已經成功進入 postgres 這個資料庫，這個資料庫儲存著一些程式的預設設定，所以請不要隨意更改甚至刪庫。 同時，程式也提示你可以使用 help 獲取命令幫助，相信你和我一樣不想看這冗長的輸出，這也是這個手冊的價值所在。
注意，從此處開始，你可以注意我們的表示習慣:

  • $ 開頭的命令代表普通命令，即使用 postgres 使用者執行的命令
  • psql=# 開頭的命令代表在進入 psql 後執行的命令，至於具體的資料庫名稱在此不標識出來，請讀者注意。
  • 大寫的DBNAME，TABLENAME 等對應你自己的資料庫名、表名等，不是實指。
$ psql  # 得到以下輸出
psql (9.x.xx)
Type "help" for help.

postgres=#

Windows下使用 psql

Windows下開啟 psql 命令窗兩種常用方法：

  • 開啟你的 pgadmin 軟體圖形介面，在選單欄找到 外掛 > PSQL Console
  • 不想開 pgadmin 的話，找到 psql.exe 所在目錄，在此目錄開啟cmd，執行：
$ psql -U postgres DBNAME

離開資料庫

請注意，Postgresql 的特殊命令都是以 \ 開頭的，而且結尾不需要分號;

psql=# \q

進入指定資料庫

請注意, 資料庫名稱大小寫是敏感的（Case-sensitive）

$ psql DBNAME # DBNAME 是你要進去的資料庫名稱

檢視（列出）所有資料庫

如果你剛接手一個資料庫，不知道現存哪些資料庫，那麼你就$ psql進入預設資料庫進行以下查詢（此查詢在其他資料庫也可以）

psql=# \list or \l

顯示每個資料庫的額外資訊，後面的 + 對 下面的 \d, \dt 也都適用，都是提供資料庫佔用之類的額外資訊。

psql=# \l+

檢視當前資料庫所有table

psql=# \dt

檢視當前資料庫所有 relation

relation 包括有 table, view, sequence等

psql=# \d

檢視某個表的所有欄位

psql=# \d TABLENAME

列出某個所有欄位（僅欄位）

與上面不同，這個僅列出欄位，便於在欄位很多，但是你有不需要所有的欄位的時候直接拷貝。（來源

psql=# SELECT * from TABLENAME where false;

檢視某資料庫佔用儲存大小

psql=# SELECT pg_size_pretty(pg_database_size('DBNAME'));

執行任意 SQL 語句

首先作為關係型資料庫，基本的 SQL 語言是必須要支援的。我假設你對此有所瞭解，當然你不需要對此精通。有一些 SQL 的基本規則你需要在這裡格外注意，稍有不慎就可能會導致錯誤，可能很簡單的錯誤就會打擊你的積極性。

SQL 語句中雙引號和單引號的使用：
  • 雙引號"用來表示的表名，但是一般我們在使用中將__雙引號省略__
  • 單引號'用來表示普通字串
    以下兩個命令是等價的：
psql # SELECT FIELDNAME FROM TABLENAME WHERE FIELDNAME='normalstring';
psel # SELECT "FIELDNAME"  FROM "TABLENAME" WHERE "FIELDNAME"='normalstring';
SQL 語句必須以 ; 結尾

匯出資料庫

命名隨意選擇，選這個字尾是為了便於標識。另外由於許可權問題，推薦儲存在 /tmp 下。
如果你每次只寫檔名的話，會預設儲存在 postgres 使用者的預設目錄下，而這個目錄比較深，你可能不太容易找，而且即使可以找到，作為普通使用者，還可能涉及到讀寫許可權問題。
Windows 下也會出現寫檔案許可權問題，有不同的解決辦法，比如修改使用者許可權等問題，但是這個涉及到使用者許可權設定等，個人不建議修改。建議自己嘗試一下，如果失敗，是不是有中文（或其他非ASCII）路徑，其次是否是在你的使用者路徑或者是否在系統路徑下。

$ pg_dump DBNAME > /tmp/DBNAME.postgresql

Windwos下從文字恢復資料庫，通常需要先建立一個空資料庫 DBNAME，然後執行：

$ psql -U postgres DBNAME < PATH\TO\YOUR\DBFILE

建立NoSQL擴充套件：

psql=# create extension hstore

在psql中執行 shell 命令：

注意中間的空格，不可忽略

psql=# \! dir

檢視postgres使用者的目錄

$ echo ~postgres

在 shell 裡面執行 SQL 語句

一般都是涉及到輸入輸出的時候使用，基本是如下格式：

$ psql -c "YOUR SQL QUERY" DATABASENAME

匯出table 或者 匯出 SQL 查詢的結果

這種 COPY 命令也是 SQL 語句的形式，但這不是 SQL 標準要求的東西
FILEPATH 不支援相對路徑

psql=# copy TABLENAME to 'FILEPATH' with delimiter '|'; -- 指定table
psql=# copy TABLENAME(FIELD1, FILED2) to 'FILEPATH' with delimiter '|'; -- 指定table及其欄位
psql=# copy (query) to 'FILEPATH' with delimiter '|'; -- 使用 query

匯出為json格式的檔案

psql=# copy (select ROW_TO_JSON(t) from (select * from TABLENAME) t) to 'FILEPATH';

在命令中表示TAB鍵

到處為 csv 的時候你可能需要用到

E'\t'

將指定表匯出至壓縮檔案

將以上命令也可以在 shell 裡面執行，這樣的話，我們就可以以檔案流的形式將我們的輸出傳到其他的程式。比如此處，我們使用 zip 將輸出的檔案直接壓縮，方便我們下載並節省流量加快速度。（請注意，這個時候，你從這個檔案解壓出來的檔名僅僅是一個連字元-，這是檔案流的預設名稱）
（這個要求你的系統安裝有 zip 。當然極少有 Linux 沒有這個軟體。Windows 如果自己有安裝，命令列可以呼叫的話也可以用，否則你就當我什麼都沒說。）

public 是你的預設 schema 。
$ psql -c "copy public.TABLENAME to stdout with delimiter E'\t' csv header"  DATABASE | zip > TABLENAME.zip

顯示配置檔案

psql=# SHOW config_file;

顯示軟體版本

psql=# SELECT version();

給某條語句的執行計時：

只輸入\timing 就是在 on 和 off之間切換，可以顯式輸入 on 和 off

psql=# \timing [on|off]

解釋語句內部處理過程：

psql=# EXPLAIN [SQL query]

相關文章