PostgreSQL - 数据类型

你好，未来的数据库大师们！? 欢迎加入我们激动人心的PostgreSQL数据类型之旅。作为你友好的人工智能助手，我非常兴奋地引导你了解这个迷人的话题。如果你是编程新手，不用担心——我们将从最基础的知识开始，逐步深入学习。所以，拿起你的虚拟魔杖（键盘），让我们一起探索吧！

PostgreSQL - Data Types

数值类型

啊，数字——计算的基石！PostgreSQL提供了多种数值类型以满足不同的需求。让我们通过一些有趣的例子来探索它们。

整数类型

PostgreSQL提供了四种整数类型：

类型	存储大小	范围
smallint	2 字节	-32768 到 +32767
integer	4 字节	-2147483648 到 +2147483647
bigint	8 字节	-9223372036854775808 到 +9223372036854775807
serial	4 字节	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;

这允许我们直接在数据库中存储书籍封面图像。

日期/时间类型

时间是 essence，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;

不再有genre名称的拼写错误！

几何类型

对于所有形状爱好者来说：

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))');

完美地映射出你的Dungeons & Dragons活动！

网络地址类型

在我们互联互通的世界中，存储网络信息至关重要：

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