Ubuntu Linux 18.04 安裝與使用 PostgreSQL 資料庫教學

PostgreSQL 官方套件庫

如果想要直接使用 PostgreSQL 官方提供的套件庫來安裝，可以改用以下的安裝方式，使用這種方式比較麻煩，但是可以有更多種 PostgreSQL 版本可以選擇，而且還有提供 pgAdmin 4 的套件。

# 安裝必要套件
sudo apt install wget ca-certificates
# 新增 PostgreSQL 套件庫
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 匯入 PostgreSQL 套件的金鑰
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 更新套件庫資訊
sudo apt update
# 安裝 PostgreSQL 套件
sudo apt install postgresql-10 pgadmin4

`psql` 管理介面

psql 是 PostgreSQL 資料庫的命令列操作介面，可以用來執行一般的 SQL 指令或是 psql 自己的 meta 指令，對於資料庫的管理以及大量工作的自動化處理非常有用。

若要進行 PostgreSQL 資料庫的設定與管理工作，必須切換成具有 PostgreSQL 管理權限的使用者（預設是 postgre ），然後再執行 psql 進入 PostgreSQL 的指令操作介面：

# 切換成 postgres 使用者
sudo -i -u postgres
# 進入 PostgreSQL 指令介面
PostgreSQL 指令操作介面
另一種方式是使用 sudo 切換成 postgres 只用者之後，直接執行 psql，這樣的話指令會更簡潔：
# 切換成 postgres 使用者，並進入 PostgreSQL 指令介面
sudo -u postgres psql
進入 psql 的環境之後，就可以執行各種指令，進行資料庫的管理工作，關於 psql 的詳細用法，可以參考 psql 的官方說明文件。
若要離開 PostgreSQL 指令介面，可以按下 Ctrl + d 這個快速鍵，或是執行 q 指令：
-- 離開 PostgreSQL 指令介面
新增 PostgreSQL 使用者帳號與資料庫
在命令列的環境下，通常管理者都會使用 psql 來管理 PostgreSQL 資料庫，但是對於新增使用者與資料庫這種常見的工作，PostgreSQL 有另外提供包裝好的 Linux 指令可用。
若要在 PostgreSQL 資料庫中新增使用者，可以用有新增帳號權限的使用者來執行 createuser 這個 Linux 指令：
# 新增 PostgreSQL 使用者
sudo -u postgres createuser gtwang
事實上 createuser 這個工具只是幫助我們產生 SQL 的指令，並送到 PostgreSQL 伺服器上執行而已，若想要觀看實際執行的 SQL 指令，可以加上 -e 參數。
若在 Linux 系統上透過本機的 Unix-domain socket 連到 PostgreSQL 資料庫，預設會使用 peer 的認證方式（直接根據 Linux 統帳號來認證，請參考 PostgreSQL 官方文件），所以不需要設定密碼，如果需要改用 TCP/IP socket 連線的話，在建立使用者時就要加上 -P 參數來設定密碼。
PostgreSQL 的使用者帳號在登入後，預設會使用跟帳號名稱相同的資料庫，我們可以利用 createdb 這個 Linux 指令建立一個跟帳號名稱相同的資料庫：
# 新增 PostgreSQL 資料庫
sudo -u postgres createdb gtwang
建立好帳號與資料庫之後，就可以使用該 Linux 帳號執行 psql 進入 PostgreSQL 資料庫使用了。
一般使用者 PostgreSQL 指令操作介面
接下來所有的 PostgreSQL 資料庫操作，都會在 psql 之中進行。
建立資料表
在使用資料庫時，首先要依照自己的資料型態，並參考 PostgreSQL 的各種資料類型，建立資料表：
-- 建立資料表
CREATE TABLE demo (
  id serial PRIMARY KEY,
  name varchar (30) NOT NULL,
  gender char(1) check (gender in ('M', 'F')),
  birthday date
建立好資料表之後，可以使用 psql 的 d 這個 meta 指令，列出目前已經建立的資料表：
-- 列出資料表
            List of relations
 Schema |    Name     |   Type   | Owner  
--------+-------------+----------+--------
 public | demo        | table    | gtwang
 public | demo_id_seq | sequence | gtwang
(2 rows)
這裡的 demo_id_seq 是用來紀錄 demo 資料表中 id 欄位的序號用的，PostgreSQL 要靠著這個來判斷下一個序號是多少，如果不想列出 sequence，可以改用 dt 指令。
d 亦可用來查看資料表的結構（相當於 MySQL 的 DESCRIBE）：
-- 檢查資料表結構
d demo
                                    Table "public.demo"
  Column  |         Type          | Collation | Nullable |             Default              
----------+-----------------------+-----------+----------+----------------------------------
 id       | integer               |           | not null | nextval('demo_id_seq'::regclass)
 name     | character varying(30) |           | not null | 
 gender   | character(1)          |           |          | 
 birthday | date                  |           |          | 
Indexes:
    "demo_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "demo_gender_check" CHECK (gender = ANY (ARRAY['M'::bpchar, 'F'::bpchar]))
新增資料可用 INSERT 這個 SQL 指令：
-- 新增資料
INSERT INTO
  demo (name, gender, birthday)
  VALUES ('Steven', 'M', '2012-03-21');
亦可一次新增多筆資料：
-- 新增多筆資料
INSERT INTO
  demo (name, gender, birthday)
  VALUES ('Mary', 'F', '2011-10-12'),
  ('Tom', 'M', '2008-01-02');
查詢資料則用 SELECT 指令：
-- 查詢資料
SELECT * FROM demo;
 id |  name  | gender |  birthday  
----+--------+--------+------------
  1 | Steven | M      | 2012-03-21
  2 | Mary   | F      | 2011-10-12
  3 | Tom    | M      | 2008-01-02
(3 rows)
若要刪除指定的資料，可用 DELETE 指令：
-- 刪除資料
DELETE FROM demo WHERE id = 3;
若要更改指定的資料，可用 UPDATE 指令：
-- 更改資料
UPDATE demo SET birthday = '2011-09-12' WHERE id = 2;
若要更改資料表的結構，可用 ALTER 指令：
-- 更改資料表，新增欄位
ALTER TABLE demo ADD weight real;
-- 更改資料表，刪除欄位
ALTER TABLE demo DROP weight;
刪除帳號、資料庫、資料表
若要刪除資料表，可用 DROP 指令：
-- 刪除資料表
DROP TABLE demo;
若要移除 PostgreSQL 的使用者帳號與資料庫，可以使用 dropuser 與 dropdb 這兩個 Linux 指令。
# 刪除 PostgreSQL 資料庫
sudo -u postgres dropdb gtwang
# 刪除 PostgreSQL 使用者
sudo -u postgres dropuser gtwang
參考資料：Tecmint、DigitalOcean、DigitalOcean、DigitalOcean

PostgreSQL 官方套件庫

psql 管理介面

新增 PostgreSQL 使用者帳號與資料庫

建立資料表

刪除帳號、資料庫、資料表

`psql` 管理介面