测验：深度Postgres：第一部分

MEDIAN 不是内置的！你需要：

PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY grade)

常见的内置聚合函数：

• MIN, MAX, COUNT
• AVG, SUM
• ARRAY_AGG, STRING_AGG
• 各种统计函数

PostgreSQL 支持三种类型转换语法：

1. ANSI SQL：CAST(expression AS type)。
2. PostgreSQL：expression::type。
3. 类型函数：type 'literal'。

它们在功能上等价，但：

• CAST() 可移植性最好。
• :: 是 PostgreSQL 特有的，但很常用。
• 中缀风格的 type 'literal' 不太常见，但依然有效。

PostgreSQL中的UNIQUE约束：

• 允许多个NULL值。
• 在唯一性检查中，NULL ≠ NULL。

要防止NULL值，请添加NOT NULL：

CREATE TABLE student_emails (
  student_id INTEGER,
  email VARCHAR(255) NOT NULL,
  UNIQUE(email)
);

区间是简化日期范围操作的强大工具！

PostgreSQL中的日期运算：

• + interval '24 hours' 增加24小时
• + interval '1 day' 增加1天
• + interval '1 month' 增加1个月
• + interval '1 year' 增加1年

结果是 2024-11-28 00:00:00。

它们都占用 8 字节，但存储的值类型不同。

那么区别在哪里？在于输入解析。

timestamptz

• 将输入归一化为绝对时间点。
• 在解析没有显式偏移量的输入时，以及在显示输出时，会考虑服务器/连接的 TimeZone 设置。

timestamp

• 存储不带时区转换的日期和时间。
• 不保留或归一化时区信息。

timestamp

• 存储不带时区信息的日期和时间。
• 适用于显式存储标准化日期，无论是 UTC 还是特定时区。

PostgreSQL 拥有丰富的数据类型，但 STRING(100) 不是其中之一。

正确的字符串类型包括：

• VARCHAR(100)（可变长度字符串）
• CHAR(100)（固定长度字符串）
• TEXT（无限长度）
• CHARACTER VARYING(100)（等同于 VARCHAR(100)）

decimal128 在许多地方（包括 Mongo 和 Java）都是常见的类型，但它不是有效的 PostgreSQL 类型，decimal 才是。

正确的数值类型包括：

• int（4 字节整数）
• bigint（8 字节整数）
• real（4 字节浮点数）
• double precision（8 字节浮点数）
• bigserial（自增 8 字节整数）
• smallserial（自增 2 字节整数）

这让你感到沮丧，甚至 生气 了吗？你不是一个人！引用一位匿名的“核心”数据库贡献者的话：“搞什么鬼，丹？！我在类型问题上栽了！太暴力了，先生！不分享我的分数了，哈。” 😈 不客气。

PostgreSQL 丰富的网络类型中并不包含 ipv4。每次我尝试使用它而不谷歌搜索时，我都会搞错。也许 macaddr8 让我觉得 一定 有 ipv4 和 ipv6 类型。不，inet 涵盖了这两者。另外，cidr 涵盖了这两者的网络掩码。

有效的网络类型包括：

• cidr（IPv4/IPv6 网络地址）
• inet（IPv4/IPv6 主机地址）
• macaddr（MAC 地址）
• macaddr8（EUI-64 MAC 地址）

PostgreSQL 拥有丰富的专用类型，但 currency 并不在其中！

有效的类型包括：

• xml（XML 数据）
• uuid（UUID）
• money（货币金额）
• interval（时间间隔）

PostgreSQL 拥有丰富的专用类型，但 triangle 不在其中。

我相信即将发布的 GEOS 版本将包含 Triangle OGC/WKT 支持，这意味着它最终应该会被包含在 Postgis 中。（基本上，这个答案在未来可能会是错误的。）

正确的专用类型包括：

• box（矩形框）
• line（无限直线）
• point（二维点）
• circle（二维圆）
• polygon（二维多边形）

PostgreSQL的integer类型是32位有符号整数，范围从-2,147,483,648到2,147,483,647。

计算256^4 = 4,294,967,296超出了这个范围。

要处理更大的数字：

-- Use BIGINT
SELECT 256::bigint * 256 * 256 * 256;

-- Or numeric for arbitrary precision
SELECT 256::numeric * 256 * 256 * 256;

PostgreSQL 时间戳具有微秒（6位小数）精度。

• 最大值：.123456（6位数字）
• 纳秒（9位数字）会被四舍五入或截断到支持的精度
• 时区偏移对于timestamptz是可接受的，但不是必需的

不太常见的陷阱： 某些语言/框架会发送纳秒精度，但PostgreSQL以微秒精度存储时间戳。

PostgreSQL 内置了几种几何和文本搜索类型，但 tsrank 不是其中之一。

正确的几何和文本搜索类型包括：

• lseg（线段）
• bytea（二进制数据）
• tsquery（文本搜索查询）
• tsvector（文本搜索文档）

NOT VALID 约束：

• 对新插入和更新的行立即检查
• 不验证现有行
• 之后可以通过 VALIDATE CONSTRAINT 验证现有行
• 对大型表很有用

没有 NOT VALID 时：

• 约束立即被检查
• 所有现有行都被验证
• 在大型表上可能很慢