skip to content
BlogZzz

[Learn] PostgreSQL 02：建立資料表與 CRUD 基本操作

/ 8 min read

Updated:
View more blogs with the tag postgresql, View more blogs with the tag sql, View more blogs with the tag database
Table of Contents

上一篇完成 PostgreSQL 的安裝、啟動與連線後，接下來要把資料真正存進 database

這篇會建立一張 users table，並依序練習 CRUD 的四種基本操作：

  • Create：新增資料
  • Read：查詢資料
  • Update：修改資料
  • Delete：刪除資料

除了 SQL 語法，也會整理 database、schema 與 table 的關係，以及 constraint 如何在資料寫入時保護資料品質

確認目前連線位置

先從 terminal 連到上一篇建立的 spring_test database

Terminal window
psql -d spring_test

進入 psql 後，確認目前的連線資訊

\conninfo

也可以用 SQL 查詢目前的 database 與 schema

SELECT current_database(), current_schema();

預期會看到 spring_test 與 public

如果查詢結果不是預期的 database，應該先切換連線，不要直接建立 table

\c spring_test

database、schema 與 table 的關係

PostgreSQL 的資料結構可以簡化成以下層級：

database cluster
└── spring_test database
    └── public schema
        └── users table
  • 一個 database cluster 可以管理多個 database
  • 一次 client connection 只能連到其中一個 database
  • 一個 database 可以包含多個 schema
  • schema 是 table、function 與 data type 等物件的 namespace
  • 不同 schema 可以包含名稱相同的 table

沒有指定 schema 時，PostgreSQL 會依照 search_path 尋找物件

SHOW search_path;

常見的預設值如下：

"$user", public

如果沒有與目前 role 同名的 schema，users 通常會解析成 public.users

在教學或 migration 中明確寫出 public.users，可以減少 table 實際建立位置不清楚的問題

建立第一張 table

建立一張用來保存使用者基本資料的 users table

CREATE TABLE public.users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  display_name text NOT NULL,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

這張 table 使用的 data type 如下：

  • bigint：可儲存範圍較大的整數
  • text：可變長度文字
  • boolean：布林值，可以是 true 或 false
  • timestamptz：帶時區語意的 timestamp，顯示時會依 session time zone 轉換

id 使用 GENERATED ALWAYS AS IDENTITY，新增資料時會由 PostgreSQL 自動產生數值

identity column 負責產生值，但本身不保證唯一，因此仍要搭配 PRIMARY KEY

constraint 如何保護資料

CREATE TABLE 中除了 data type，也定義了幾種 constraint：

  • PRIMARY KEY：識別每一筆資料，值必須唯一且不能是 NULL
  • NOT NULL：欄位不能是 NULL
  • UNIQUE：相同欄位值不能重複
  • DEFAULT：省略欄位時使用預設值

這些規則由 PostgreSQL 執行，不管資料來自 psql、Spring Boot 或其他應用程式，都必須符合相同條件

DEFAULT 只會在欄位被省略或明確寫成 DEFAULT 時生效，主動寫入 NULL 並不會自動改用預設值

另外，NOT NULL 只會拒絕 NULL，不會拒絕空字串

如果 display_name 連空字串也不能接受，可以再加入 CHECK constraint，這部分適合留到後續介紹 constraint 時討論

查看 table 結構

建立完成後，先確認目前 schema 中有哪些 table

\dt

查看 users 的欄位、data type、default 與 index

\d public.users

如果 \dt 找不到剛建立的 table，可以依序檢查：

  • 目前連到哪一個 database
  • table 建立在哪一個 schema
  • search_path 是否包含該 schema

Create：新增資料

新增第一位使用者時，不需要提供 id、is_active 與 created_at，PostgreSQL 會產生 identity value 或套用 default

INSERT INTO public.users (email, display_name)
VALUES ('alice@example.com', 'Alice')
RETURNING id, email, display_name, is_active, created_at;

RETURNING 可以直接取得剛寫入的資料，不需要再執行一次 SELECT

也可以在同一個 statement 中新增多筆資料

INSERT INTO public.users (email, display_name)
VALUES
  ('bob@example.com', 'Bob'),
  ('carol@example.com', 'Carol')
RETURNING id, email;

因為 email 有 UNIQUE constraint，再次新增相同 email 時會失敗

INSERT INTO public.users (email, display_name)
VALUES ('alice@example.com', 'Another Alice');

這不是應用程式額外判斷出來的結果，而是 database 拒絕了不符合 constraint 的資料

Read：查詢資料

查詢全部欄位與全部資料，可以使用：

SELECT *
FROM public.users
ORDER BY id;

正式查詢通常只選取需要的欄位，並搭配 WHERE、ORDER BY 與 LIMIT

SELECT id, email, display_name, created_at
FROM public.users
WHERE is_active = true
ORDER BY created_at DESC, id DESC
LIMIT 10;
  • WHERE 篩選符合條件的資料
  • ORDER BY created_at DESC, id DESC 依建立時間由新到舊排序，時間相同時再依 id 排序
  • LIMIT 10 最多取回十筆資料

SQL query 沒有使用 ORDER BY 時，資料順序沒有保證

當 LIMIT 用來取得固定範圍的資料時，通常應該搭配能產生穩定順序的 ORDER BY

Update：修改資料

把 Alice 的顯示名稱改成 Alice Chen

UPDATE public.users
SET display_name = 'Alice Chen'
WHERE email = 'alice@example.com'
RETURNING id, email, display_name;

RETURNING 會回傳實際被修改的資料，也能幫助確認 WHERE 是否選到預期的 row

如果省略 WHERE，table 中的所有 row 都會被修改

執行前應該先確認篩選條件，必要時可以先用相同的 WHERE 執行 SELECT

SELECT id, email, display_name
FROM public.users
WHERE email = 'alice@example.com';

Delete：刪除資料

刪除 Bob 的資料

DELETE FROM public.users
WHERE email = 'bob@example.com'
RETURNING id, email;

DELETE 的 RETURNING 會回傳被刪除前的 row 內容

和 UPDATE 一樣，如果省略 WHERE，table 中的所有 row 都會受到影響

在不確定條件是否正確時，先用相同的 WHERE 執行 SELECT，確認後再改成 DELETE

確認最後結果

完成新增、修改與刪除後，再查詢一次 users

SELECT id, email, display_name, is_active, created_at
FROM public.users
ORDER BY id;

預期結果會保留 Alice 與 Carol，其中 Alice 的 display_name 已經更新，Bob 則已經被刪除

identity value 在資料刪除後不會自動重新編號，因此 id 中間出現空號是正常現象

id 的用途是穩定識別 row，不是呈現連續的資料筆數

結論

  • PostgreSQL connection 一次只會進入一個 database，schema 則負責組織 database 內的物件
  • GENERATED ALWAYS AS IDENTITY 負責產生 id，PRIMARY KEY 才負責唯一與非空限制
  • constraint 能在 database 層阻止不合法的資料
  • RETURNING 可以直接取得 INSERT、UPDATE 與 DELETE 影響的 row
  • SELECT 沒有使用 ORDER BY 時不保證資料順序
  • UPDATE 與 DELETE 缺少 WHERE 時會影響所有 row

下一篇可以在這張 users table 的基礎上，加入另一張 table 與 FOREIGN KEY，開始練習一對多關聯

參考資料