PostgreSQL - 數據類型

你好，未來的數據庫大師！? 歡迎來到我們精彩的 PostgreSQL 數據類型之旅。作為你們親切鄰居的計算機科學老師，我非常高興能夠帶領你們進行這個引人入勝的主題。如果你是编程新手，別擔心——我們會從最基礎的知識開始，然後逐步深入。所以，拿起你的虛擬魔杖（鍵盤），讓我們一起進入吧！

PostgreSQL - Data Types

數值類型

啊，數字——計算的基石！PostgreSQL 提供了多種數值類型以滿足不同需求。讓我們通過一些有趣的例子來探索它們。

整數類型

PostgreSQL 提供了四種整數類型：

類型	�儲存大小	范圍
smallint	2 bytes	-32768 到 +32767
integer	4 bytes	-2147483648 到 +2147483647
bigint	8 bytes	-9223372036854775808 到 +9223372036854775807
serial	4 bytes	1 到 2147483647

讓我們創建一個表來存儲我們最喜歡的書籍信息：

CREATE TABLE favorite_books (
id serial PRIMARY KEY,
title VARCHAR(100),
publication_year smallint,
pages integer,
isbn bigint
);

在這個例子中：

id 使用 serial，它會自動為每個新條目增加。
publication_year 使用 smallint，因為書籍並不是那麼古老（對不起，古老的卷軸！）。
pages 使用 integer 來表示書的長度。
isbn 使用 bigint 來表示那些長的 ISBN 數字。

運算類型

當你需要小數精度的時候：

類型	描述
real	6 位小數精度
double precision	15 位小數精度

讓我們為我們的書籍表添加更多細節：

ALTER TABLE favorite_books
ADD COLUMN rating real,
ADD COLUMN price double precision;

現在我们可以存儲書籍評分（例如，4.5 星）和精確價格。

任意精度的數字

當你需要極端精度的時候：

CREATE TABLE scientific_constants (
name VARCHAR(50),
value numeric(1000, 900)
);

INSERT INTO scientific_constants (name, value)
VALUES ('Pi', 3.14159265358979323846264338327950288419716939937510);

numeric(1000, 900) 允許最多 1000 位數，小數點後有 900 位。對於那些 π 狂熱者來說，這簡直完美！

貨幣類型

錢財讓世界運轉，PostgreSQL 為此提供了剛好的類型：

CREATE TABLE product_prices (
product_name VARCHAR(50),
price money
);

INSERT INTO product_prices (product_name, price)
VALUES ('豪華筆', '$9.99');

money 類型會自動處理貨幣符號和逗號分隔符。

字符類型

文本在數據庫中無處不在。PostgreSQL 提供了三種主要的字符類型：

類型	描述
character(n)	固定長度，空白填充
varchar(n)	變長度，有限制
text	變長度，無限制

讓我們在書籍數據庫中使用這些類型：

ALTER TABLE favorite_books
ADD COLUMN author character(50),
ADD COLUMN description varchar(500),
ADD COLUMN full_text text;

author 固定為 50 個字符（希望這對 "J.K. Rowling" 足夠！）。
description 可以變化，但不會超過 500 個字符。
full_text 可以存儲整本書，如果我们要的話（但還是不要這麼做吧？）。

二進制數據類型

有時候，你需要存儲原始二進制數據。這時可以使用 bytea：

ALTER TABLE favorite_books
ADD COLUMN cover_image bytea;

-- 插入圖像（假設這是實際的二進制數據）
UPDATE favorite_books
SET cover_image = '\x89504E470D0A1A0A'
WHERE id = 1;

這讓我們可以直接在數據庫中存儲書籍封面圖像。

日期/時間類型

時間是關鍵，PostgreSQL 也為此提供了支持：

類型	描述
date	日期（沒有時間）
time	時間（沒有日期）
timestamp	日期和時間
interval	時間間隔

讓我們追踪我們閱讀最喜歡的書籍的時間：

ALTER TABLE favorite_books
ADD COLUMN purchase_date date,
ADD COLUMN reading_start_time time,
ADD COLUMN last_read timestamp,
ADD COLUMN reading_duration interval;

UPDATE favorite_books
SET purchase_date = '2023-04-01',
reading_start_time = '20:30:00',
last_read = '2023-04-15 22:15:30',
reading_duration = '2 hours 30 minutes'
WHERE id = 1;

現在我們可以分析我們的閱讀習慣了！?⏱️

布爾類型

有時候，生活就是二進制的：真或假，1 或 0，是或不是：

ALTER TABLE favorite_books
ADD COLUMN is_favorite boolean;

UPDATE favorite_books
SET is_favorite = true
WHERE rating > 4.5;

簡單而有效！

枚舉類型

當你有一組固定的選項時，枚舉是你的好夥伴：

CREATE TYPE book_genre AS ENUM ('小說', '非小說', '科學', '歷史');

ALTER TABLE favorite_books
ADD COLUMN genre book_genre;

UPDATE favorite_books
SET genre = '小說'
WHERE id = 1;

這樣就不會再在類型名稱上出現打字錯誤了！

几何類型

對於所有喜歡形狀的你們：

CREATE TABLE geometric_objects (
id serial PRIMARY KEY,
name VARCHAR(50),
location point,
area box
);

INSERT INTO geometric_objects (name, location, area)
VALUES ('我的房子', '(10,20)', '((0,0),(100,100))');

這對於製作你的龍與地下城冒險地圖來說非常完美！

網絡地址類型

在我們這個互聯互通的世界中，存儲網絡信息是至關重要的：

CREATE TABLE network_devices (
id serial PRIMARY KEY,
name VARCHAR(50),
ip inet,
mac macaddr
);

INSERT INTO network_devices (name, ip, mac)
VALUES ('我的路由器', '192.168.1.1', '08:00:2b:01:02:03');

現在你可以在你的網絡中追踪所有的設備了！

位串類型

有時候，你需要直接與原始位操作：

CREATE TABLE permissions (
user_id integer,
access_flags bit(8)
);

INSERT INTO permissions (user_id, access_flags)
VALUES (1, B'10110000');

每個位可以代表不同的權限。這種存儲方式效率多高！

全文搜索類型

PostgreSQL 內置了全文搜索支持：

ALTER TABLE favorite_books
ADD COLUMN search_vector tsvector;

UPDATE favorite_books
SET search_vector = to_tsvector('english', title || ' ' || description);

CREATE INDEX textsearch_idx ON favorite_books USING GIN (search_vector);

現在你可以在你的書籍收藏中进行快速搜索！

UUID 類型

通用唯一標識符對於分布式系統來說非常棒：

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE distributed_data (
id uuid DEFAULT uuid_generate_v4(),
data TEXT
);

INSERT INTO distributed_data (data) VALUES ('一些分布式数据');

再也不用擔心不同服務器之間的 ID 冲突了！

XML 類型

當你需要存儲結構化的 XML 數據時：

CREATE TABLE xml_documents (
id serial PRIMARY KEY,
doc xml
);

INSERT INTO xml_documents (doc)
VALUES (
'<book>
<title>PostgreSQL 入門</title>
<author>你最喜欢的老師</author>
</book>'
);

數據庫中也可以存儲 XML？為什麼不呢！

JSON 類型

在 Web 服務的時代，JSON 是王者：

ALTER TABLE favorite_books
ADD COLUMN metadata jsonb;

UPDATE favorite_books
SET metadata = '{"tags": ["教育", "有趣"], "reviews": [{"user": "Alice", "rating": 5}, {"user": "Bob", "rating": 4}]}'
WHERE id = 1;

jsonb 類型允許高效地存儲和查詢 JSON 數據。

陣列類型

有時候，你需要在一個列中存儲多個值：

ALTER TABLE favorite_books
ADD COLUMN keywords text[];

UPDATE favorite_books
SET keywords = ARRAY['數據庫', '編程', '太棒了']
WHERE id = 1;

陣列讓你可以不用創建獨立的表就能存儲列表值。

複合類型

通過組合現有的類型，創建自己的自定義類型：

CREATE TYPE address AS (
street VARCHAR(100),
city VARCHAR(50),
country VARCHAR(50)
);

ALTER TABLE favorite_books
ADD COLUMN publisher_address address;

UPDATE favorite_books
SET publisher_address = ROW('123 書籍街', '圖書城', '書籍國')
WHERE id = 1;

複合類型幫助你更邏輯地組織相關數據。

範圍類型

當你需要表示一組範圍的值時：

CREATE TABLE hotel_bookings (
id serial PRIMARY KEY,
room_number integer,
stay daterange
);

INSERT INTO hotel_bookings (room_number, stay)
VALUES (101, '[2023-07-01, 2023-07-07)');

daterange 類型讓檢查預訂重疊變得非常容易！

對象標識符類型

這些類型是 PostgreSQL 內部使用的：

CREATE TABLE table_info (
table_name VARCHAR(50),
oid oid
);

INSERT INTO table_info (table_name, oid)
SELECT 'favorite_books', 'favorite_books'::regclass::oid;

你可能不常使用它們，但它們對 PostgreSQL 的內部運作至關重要。

準則類型

這些不是真正的類型，但它們用於函數聲明：

CREATE FUNCTION multiply_any(anyelement, anyelement)
RETURNS anyelement AS $$
SELECT $1 * $2;
$$ LANGUAGE SQL;

SELECT multiply_any(5, 3);  -- 返回 15
SELECT multiply_any(5.5, 2);  -- 返回 11.0

像 anyelement 這樣的準則類型允許更靈活的函數定義。

而這就是 PostgreSQL 數據類型的廣闊領域。記住，選擇正確的數據類型對於性能、存儲效率和數據完整性至關重要。這就像選擇正確的工具來完成工作一樣——你總不會用大锤來掛畫吧？

在你繼續 PostgreSQL 的旅程中，你會發現更多這些數據類型的微妙之處和技巧。繼續實驗，保持好奇心，最重要的是，玩得開心！誰知道呢，你可能會成為城裡下一個數據庫大師。?‍♂️?

現在你們就去創建令人驚奇的數據庫吧！並記住，在 PostgreSQL 的世界中，每種數據類型都有它自己的位置——就像每本書在書架上都有它完美的小角落一樣。開心編程！??

Credits: Image by storyset