PostgreSQL 入門學習筆記 資料型態與DDL

PostgreSQL 的歷史可追溯到 1980 年代美國加州柏克萊大學的 POSTGRES 專案，由電腦科學教授 Michael Stonebraker 帶領團隊開發。團隊的目標是：「打造一個可擴充、支援複雜資料結構的資料庫系統」。當時 MySQL 甚至還沒誕生，SQL Server 還是微軟與 Sybase 合作的產品。

PostgreSQL 有超強的資料型態系統，支援 JSON、陣列、地理座標（PostGIS）、範圍型態、網路型態（CIDR, INET）等。甚至可以自訂型態、運算子與函數。還有從設計階段就全面支援交易的一致性與隔離性，採用 MVCC 架構讓讀寫可同時進行而不互鎖，並提供精細的行鎖與 Savepoint 控制。相較於 MySQL 偏重效能與寬鬆一致性，PostgreSQL 更注重資料正確、安全與穩定，特別適合金融、訂單等需要高可靠度的系統。

本篇文章教學是給已經有其他資料庫基礎的情況下快速了解 PostgreSQL 的差別，也可以當作是快速理解資料庫基本知識的參考。

安裝與基本操作

本篇教學直接使用 Docker Image ，使用 PostgreSQL 18。也可以直接上官網安裝，或是依照不同系統使用 Homebrew、apt-get、yum、手動下載安裝檔…等方式安裝。

透過Docker安裝

安裝好 Docker，建立資料夾，在裡面建立一個檔案: docker-compose.yml ，輸入以下內容。

services:
  postgres:
    image: postgres:18
    container_name: postgres
    restart: always
    environment:
      POSTGRES_USER: user    # 預設帳號
      POSTGRES_PASSWORD: pwd # 預設密碼
      POSTGRES_DB: mydb      # 預設資料庫
      TZ: Asia/Taipei
    ports:
      - "5432:5432"
    volumes:
      - ./postgres-data:/var/lib/postgresql

儲存後使用終端機切換到剛才新建的資料夾，輸入以下指令啟動。第一次啟動 Docker 會下載 Image 需要一點時間。

docker compose up -d

登入資料庫系統

使用 docker ps 可以查看目前啟動的容器，使用 docker compose logs -f 可以查看容器內的 Log 紀錄。啟動好之後就可以透過以下指令進入 DB。PostgreSQL 會預設使用跟 Username 相同的資料庫，除非我們剛好也有同名資料庫，不然會報錯說找不到資料庫，在此直接需要指定資料庫名稱。

docker exec -it postgres psql -U user -d mydb

這邊是透過 Docker compose 啟動參數讓 PostgreSQL 自動建立一個資料庫，如果沒有自動建立的資料庫，可以使用預設的資料庫。

docker exec -it postgres psql -U user -d postgres

基本操作

許多人一開始學習資料庫可能是從 MySQL 開始，這邊提供兩邊的指令對照，方便比較。因為 MySQL、MariaDB、SQLite 等資料庫後面沒有再分 Schema，資料庫之後直接是資料表，所以有一些指令是沒有對應的。

功能	PostgreSQL	MySQL / MariaDB
切換資料庫	\connect db_name 或 \c db_name	USE db_name
切換schema	SET search_path TO schema_name;	沒有
列出所有資料庫	\list 或 \l	SHOW DATABASES;
列出schema	\dn	沒有
列出資料表	\dt	SHOW TABLES;
列出不同schema的資料表	\dt *.*	沒有
列出指定schema的資料表	\dt schema_name.*	沒有
顯示欄位結構	\d table_name	DESC table_name;
查詢目前所在資料庫	SELECT current_database();	SELECT DATABASE();
查詢目前登入身份	SELECT current_user;	SELECT USER();
離開互動介面	\q	exit

補充說明，在 \l \dt \dn 這些指令可以加上加號顯示更多詳細資訊，分別是 \l+ \dt+ \dn+。

DDL 資料定義語言

DDL（Data Definition Language）定義語言，在這邊學習如何建立修改刪除 Database、Schema、Table。

• 一個資料庫管理系統（如 PostgreSQL）可以有多個資料庫（Database）
• 一個資料庫可以有多個結構（Schema）
• 一個結構可以有多跟資料表（Table）
• 一個資料表底下可以有多個欄位（Column）

Database 資料庫

PostgreSQL 是資料庫管理系統，安裝一個 PostgreSQL 底下可以建立很多個 Database。

Create

-- 建立資料庫
CREATE DATABASE db_name;

-- 建立資料庫，加上詳細設定
CREATE DATABASE db_name
    WITH OWNER = myuser
         ENCODING = 'UTF8'
         LC_COLLATE = 'zh_TW.UTF-8'
         LC_CTYPE = 'zh_TW.UTF-8'
         TEMPLATE = template0;

Alter

-- 修改資料庫名稱
ALTER DATABASE old_name RENAME TO new_name;

-- 將資料庫的擁有者改為 new_owner
ALTER DATABASE db_name OWNER TO new_owner;

-- 設定該資料庫的預設 search_path
-- 表示使用者連線進這個資料庫時，會自動套用這個 schema 搜尋順序
ALTER DATABASE db_name SET search_path TO myapp, public;

-- 設定該資料庫的預設時區
-- 所有在這個資料庫連線中產生的時間（如 now()）都會使用這個時區
ALTER DATABASE db_name SET timezone TO 'Asia/Taipei';

需要注意，不能在使用資料庫的時候對它更名，可以先使用指令 \c postgres 移動到主資料庫，再去修改，不然會出現錯誤訊息：

ERROR: cannot be connected to the target database

Delete

DROP DATABASE db_name;

跟上面更改資料庫名稱一樣，不能在連著資料庫的時候刪除它，不然會出現：

ERROR: cannot drop the currently open database

Schema 結構

也可以把 Schema 理解為命名空間，一個資料庫底下可以有好多個 Schma。PostgreSQL 會在每個資料庫底下建立一個預設的 Schema 名為 public。

Create

-- 建立 Schema
CREATE SCHEMA schema_name;

-- 指定擁有者
CREATE SCHEMA schema_name AUTHORIZATION username;

Alter

-- 修改 Schema 名稱
ALTER SCHEMA old_name RENAME TO new_name;

-- 改變擁有者
ALTER SCHEMA schema_name OWNER TO new_owner;

Delete

-- 刪除 Schema
DROP SCHEMA schema_name;

-- 連同所有物件強制刪除
DROP SCHEMA schema_name CASCADE;

Table 資料表

Create

-- 建立資料表
CREATE TABLE table_name (
  column_name data_type [constraint],
  ...
);

-- 範例
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 指定 Schema 的資料表
CREATE TABLE myapp.customers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

要指定 Schema 之前記得要先建立對應的 Schema。在第一個範例中沒有指定 Schema ，因此會存放到預設的 public Schema。

-- 複合主鍵（Composite Key）範例
CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id)
);

-- 外來鍵（Foreign Key）範例
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total NUMERIC(10,2) NOT NULL
);

-- 加上索引
CREATE INDEX idx_users_email ON users(email);

-- 加上唯一索引
CREATE UNIQUE INDEX unique_users_email ON users(email);

需要注意 PostgreSQL 的索引名稱是同一個 Schema 內不可重複的，跟 SQLite 一樣，跟 MySQL、MariaDB、SQL Server 相同 Table 內不可重複的限制不一樣。

Alter

-- 對資料表新增欄位
ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

-- 修改欄位型態
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

-- 修改欄位預設值
ALTER TABLE users ALTER COLUMN active SET DEFAULT TRUE;

-- 移除欄位
ALTER TABLE users DROP COLUMN password_reset_token;

Delete

-- 刪除資料表
DROP TABLE users;

-- 包含相依關聯（外鍵等）強制刪除資料表
DROP TABLE users CASCADE;