PostgreSQL

Appearance

📘 PostgreSQL 常量：数据表达的基石 ​

PostgreSQL 常量是 SQL 查询中的固定值表示法，就像数学公式中的数字、文本中的固定词语，是构建数据库操作的基础元素。掌握常量表示法，能让你的 SQL 查询更精准高效！

🌟 常量类型全景图 ​

PostgreSQL 支持三种核心常量类型：

📜 字符串常量详解 ​

基础字符串表示 ​

用单引号包裹文本内容：

sql
SELECT '数据库教程';  -- 基本字符串示例 ✅

处理特殊字符 ​

sql
-- 单引号转义（双写单引号）
SELECT 'O''Reilly 出版社';  -- 结果: O'Reilly 出版社

-- 换行符处理
SELECT '第一行'
'第二行';  -- 结果: 第一行第二行

转义字符串（C 风格） ​

在单引号前加 E 启用转义：

sql
SELECT E'标题\t价格\n------\t------';  -- \t=制表符, \n=换行
转义序列功能示例
\b退格E'退\b格' → 格
\n换行E'换\n行'
\x4A十六进制字符E'\x4A' → J
\\反斜杠本身E'路径\\文件'

WARNING

转义安全提醒：
默认 standard_conforming_strings=on 时，只有带 E 前缀的字符串才解析转义序列。关闭此设置会导致安全风险！

Unicode 字符串 ​

使用 U& 前缀处理多语言：

sql
-- 中文示例
SELECT U&'\4E2D\6587';  -- 结果: 中文

-- 自定义转义符
SELECT U&'!4E2D!6587' UESCAPE '!';  -- 结果: 中文

💰 美元引号字符串 ​

处理复杂文本的终极方案：

sql
-- 免转义处理含引号文本
SELECT $$包含'单引号'和"双引号"的文本$$;

-- 带标签的嵌套（PL/pgSQL 函数示例）
CREATE OR REPLACE FUNCTION format_name(v_name TEXT) 
RETURNS TEXT AS $func$
BEGIN
    RETURN $nested$尊敬的$nested$ || v_name;
END;
$func$ LANGUAGE plpgsql;

🔢 数值常量精要 ​

常规数值表示 ​

sql
SELECT 
  42 AS 整数,          -- 整数
  3.1415 AS 圆周率,    -- 小数
  1.925e-3 AS 科学计数, -- 0.001925
  1_000_000 AS 百万;   -- 使用下划线增强可读性

进制转换演示 ​

sql
SELECT 0b1101; -- 结果: 13
sql
SELECT 0o755;  -- 结果: 493（权限表示）
sql
SELECT 0xFF;    -- 结果: 255（颜色值）

⚡ 类型转换技巧 ​

四种将常量转为特定类型的方法：

sql
SELECT DATE '2023-10-01';  -- 显式日期类型
sql
SELECT 3.14::REAL;         -- 转为单精度浮点数
sql
SELECT CAST('100' AS INTEGER); -- 字符串转整数
sql
SELECT numeric('9.99');     -- 创建数值类型

🚀 三大实际应用场景 ​

场景 1：多语言用户注册系统 ​

业务背景：国际电商平台需支持用户输入多语言姓名和特殊字符

sql
-- 创建用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    reg_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入包含特殊字符的数据
INSERT INTO users (name) VALUES 
  (U&'Andr\00E9' UESCAPE '\'),     -- 法语名 André
  (U&'\5C0F\6797'),                -- 日语名 小林
  ($$O'Neil$$);                    -- 含撇号的英文名

-- 查询结果
SELECT * FROM users;

处理结果：

idnamereg_time
1André2023-10-01 09:30
2小林2023-10-01 09:30
3O'Neil2023-10-01 09:30

💡 价值：Unicode 转义和美元引号确保全球姓名准确存储，避免乱码或截断问题

场景 2：硬件设备状态监控 ​

业务背景：IoT 系统需高效存储传感器发送的二进制状态码

sql
-- 创建设备状态表
CREATE TABLE device_status (
    device_id INT PRIMARY KEY,
    status BIT(8) NOT NULL  -- 8位状态码
);

-- 插入二进制状态数据
INSERT INTO device_status VALUES 
  (101, B'10101010'),  -- 二进制直接量
  (102, X'FF');        -- 十六进制等价形式

-- 查询设备102状态
SELECT device_id, status::TEXT AS binary_status 
FROM device_status WHERE device_id = 102;

处理结果：

device_idbinary_status
10211111111

⚙️ 技术优势：位串常量精准表达硬件状态，比文本节省 75% 存储空间

场景 3：金融精度计算 ​

业务背景：支付系统需确保金额计算无精度损失

sql
-- 创建交易表
CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(10,2) NOT NULL
);

-- 插入不同格式的金额
INSERT INTO transactions (amount) VALUES 
  (1000.50),          -- 标准小数
  (1e3),              -- 科学计数法
  (0.1 + 0.2);        -- 浮点运算测试

-- 精确金额查询
SELECT id, amount::TEXT FROM transactions;

处理结果：

idamount
11000.50
21000.00
30.30

⚠️ 关键发现：
金融计算必须使用 NUMERIC 类型！REAL/FLOAT 会导致精度损失：

sql
SELECT (0.1::REAL + 0.2::REAL) = 0.3::REAL; -- 结果: false ❌

💎 核心要点总结 ​

  1. 字符串处理三剑客

    • 普通文本 → 单引号 '文本'
    • 特殊字符 → 转义字符串 E'\n\t'
    • 多语言 → Unicode U&'\4E2D'
    • 复杂内容 → 美元引号 $$内容$$

  2. 数值精度黄金法则

  3. 最佳实践清单 ✅

    • 多语言系统优先使用 Unicode 转义
    • 硬件通信采用位串节省空间
    • 金额计算强制指定 NUMERIC 类型
    • 复杂文本使用美元引号避免转义地狱

🚀 终极技巧：在 SQL 中直接运行 SELECT 格式'常量' 测试类型转换，快速验证常量表示是否正确！

掌握这些常量表达技巧，你的 PostgreSQL 查询将如虎添翼！ 🐘✨