【PostgreSQL】【第2章】PostgreSQL基本操作

1. 单引号与双引号的区别

核心要点

• 单引号：用于标识实际值（如字符串、日期）。

  SELECT '张三' AS name; -- 正确
  

• 双引号：用于标识关键字（如表名、字段名）。

  SELECT "age" FROM "User"; -- 正确（"age" 为字段名）
  

注意事项

• 从 MySQL 切换到 PostgreSQL 时需特别注意：
  • 错误示例：SELECT "日本";（若“日本”非关键字，会报错）。

---

2. 数据类型转换

转换方法

1. 前缀声明：

   SELECT BIT '0101'; -- 转换为位图类型
   SELECT DATE '2023-10-01'; -- 转换为日期类型
   

2. 双冒号后缀：

   SELECT '0101'::BIT; -- 转换为位图类型
   SELECT '2023-10-01'::DATE; -- 转换为日期类型
   

3. 显式函数：

   SELECT CAST('100' AS INT); -- 使用 CAST 函数
   
   

---

3. 布尔类型

特性

• 可存储 TRUE、FALSE 和 NULL。
• 支持隐式转换（如 'yes'::BOOLEAN 返回 TRUE）。

逻辑操作结果表

A	B	A AND B	A OR B
TRUE	TRUE	TRUE	TRUE
TRUE	NULL	NULL	TRUE
FALSE	NULL	FALSE	NULL
NULL	NULL	NULL	NULL

---

4. 数值类型

分类

1. 整型：
   • SMALLINT（2字节）、INTEGER（4字节）、BIGINT（8字节）。
2. 浮点型：
   • NUMERIC（精确小数）、REAL（单精度）、DOUBLE PRECISION（双精度）。
3. 序列：

   CREATE SEQUENCE user_id_seq; -- 创建序列
   SELECT nextval('user_id_seq'); -- 获取下一个值
   

示例

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY, -- 自增主键
  balance NUMERIC(10, 2)    -- 精确小数
);

---

5. 数值类型操作

运算符

• 幂运算：SELECT 2^3; → 8
• 位运算：SELECT 31 & 16; → 16
• 函数：

  SELECT ROUND(3.1415, 2); -- 四舍五入
  SELECT ABS(-5);          -- 绝对值
  

---

6. 字符串类型操作

类型与函数

• 定长字符串：CHAR(10)
• 变长字符串：VARCHAR(255) 或 TEXT
• 拼接操作：

  SELECT 'Hello' || ' World'; -- 使用 || 拼接
  

函数示例

SELECT SUBSTRING('PostgreSQL', 1, 5); -- 返回 'Postg'
SELECT UPPER('hello');                -- 返回 'HELLO'

---

7. 日期类型操作

核心类型

• DATE：年月日
• TIME：时分秒
• TIMESTAMP：日期+时间

示例

SELECT CURRENT_DATE;                     -- 当前日期
SELECT TIMESTAMP '2023-10-01' + INTERVAL '1 month'; -- 加一个月

---

8. 枚举类型操作

创建与使用

1. 定义枚举：

   CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed');
   

2. 应用枚举：

   CREATE TABLE schedule (
     day week -- 字段类型为枚举
   );
   INSERT INTO schedule (day) VALUES ('Mon'); -- 正确
   INSERT INTO schedule (day) VALUES ('Fri'); -- 报错（非法值）
   

优点

• 限制字段取值范围，避免无效数据。

---

10-24. IP类型操作

核心功能

• 存储IP地址：支持IPv4/IPv6格式，规范存储格式。
• 校验功能：自动校验IP合法性（如256非法）。
• 范围查找：支持基于CIDR表示法的IP范围查询（传统MySQL字符串存储无法实现）。

操作示例

-- 创建表（cidr类型存储IP）
CREATE TABLE test (
    id BIGSERIAL,
    ip_address CIDR
);

-- 插入数据
INSERT INTO test (ip_address) VALUES ('192.168.1.1/24');

-- 范围查询（查找192.168.1.0/24网段）
SELECT * FROM test WHERE ip_address << '192.168.1.0/24';

优势对比

特性	PostgreSQL (cidr)	MySQL (字符串)
格式校验	✅	❌
子网范围查询	✅	❌
存储优化	✅	❌

---

11-25 & 12-26. JSON类型操作

JSON vs JSONB

特性	JSON	JSONB
索引支持	❌	✅
保留空格/格式	✅	❌（自动去除）
键顺序保留	✅	❌
重复键处理	保留最后一个值	保留最后一个值
存储方式	文本存储	二进制序列化

操作示例

-- 创建表（含JSON和JSONB字段）
CREATE TABLE test (
    id BIGSERIAL,
    data JSON,
    data_b JSONB
);

-- 插入数据
INSERT INTO test (data, data_b) VALUES (
    '{"name": "张三", "age": 23, "birthday": "1999-01-01"}',
    '{"name": "李四", "age": 25, "birthday": "2000-01-01"}'
);

-- 查询JSON字段中的值
SELECT data->>'name' AS name FROM test;

注意事项

1. 字符串需双引号包裹：SELECT '{"key": "value"}'::JSON; ✅
   SELECT '{'key': 'value'}'::JSON; ❌
2. JSONB索引创建：

CREATE INDEX idx_data_b ON test USING GIN (data_b);

---

13-27. 复合类型操作

概念

• 类似Java对象嵌套：将复杂结构映射为数据库类型，避免多表关联。
• 示例：用户表包含info字段，存储姓名和年龄。

操作示例

-- 创建复合类型
CREATE TYPE user_info AS (
    name VARCHAR(50),
    age INT
);

-- 创建表使用复合类型
CREATE TABLE tb_user (
    id BIGSERIAL,
    info user_info
);

-- 插入数据
INSERT INTO tb_user (info) VALUES (('张三', 23));

---

14-28 & 15-29. 数组类型操作

声明与使用

-- 创建表（支持一维/多维数组）
CREATE TABLE test (
    id BIGSERIAL,
    numbers INT[],
    matrix INT[][]
);

-- 插入数据
INSERT INTO test (numbers, matrix) VALUES (
    '{1,2,3}',
    '{{4,5}, {6,7}}'
);

-- 查询数组元素
SELECT numbers[1] AS first_element FROM test;  -- 索引从1开始

特殊符号处理

场景	解决方案
单引号	转义为两个单引号 ''
逗号分隔冲突	外层用双引号包裹 "a,b"
多维数组	嵌套大括号 {{1,2}, {3}}

集合操作

-- 包含（@>）
SELECT ARRAY[1,2,3] @> ARRAY[2];  -- ✅

-- 被包含（<@）
SELECT ARRAY[2] <@ ARRAY[1,2,3];  -- ✅

-- 交集（&&）
SELECT ARRAY[1,2] && ARRAY[2,3];  -- ✅

---

总结

PostgreSQL通过丰富的数据类型（IP、JSON/B、复合类型、数组）显著提升业务场景适配性：

1. IP类型：支持校验和子网计算。
2. JSON/B：JSONB适合频繁查询场景（索引优化）。
3. 复合类型：减少多表关联，简化模型。
4. 数组：灵活存储集合数据，支持多维和集合操作。