PostgreSQL

Appearance

常量 ​

PostgreSQL中的常量是在SQL查询中直接表示固定值的表达式。理解常量的表示方法对于编写有效的SQL查询至关重要。

常量类型概述 ​

PostgreSQL支持三种基本类型的常量：

  • 字符串常量
  • 位串常量
  • 数值常量

除了这些隐式类型的常量外，还可以使用显式类型指定来创建更精确的常量表示。

字符串常量 ​

基本字符串 ​

字符串常量是用单引号(')括起来的任意字符序列。

sql
-- 基本字符串常量
SELECT 'Hello World';

要在字符串中包含单引号，需要连续使用两个单引号：

sql
-- 包含单引号的字符串
SELECT 'Dianne''s horse';  -- 结果: Dianne's horse

🔍 注意：字符串中的单引号要用两个单引号('')表示，而不是双引号(")。

跨行字符串 ​

如果两个字符串常量仅由空白字符（且包含至少一个换行符）分隔，它们会被自动连接：

sql
SELECT 'Hello'
'World';  -- 等同于 SELECT 'HelloWorld';

但是这样是无效的：

sql
SELECT 'Hello'      'World';  -- 错误！

C风格转义字符串 ​

PostgreSQL支持带C风格转义序列的字符串常量，在单引号前加上字母E（大写或小写）：

sql
SELECT E'Line 1\nLine 2';  -- \n表示换行

转义序列表 ​

转义序列描述
\b退格
\f换页
\n换行
\r回车
\t制表符
\o, \oo, \ooo (o = 0-7)八进制字节值
\xh, \xhh (h = 0-9, A-F)十六进制字节值
\uxxxx, \Uxxxxxxxx (x = 0-9, A-F)16或32位Unicode字符值

在转义字符串中：

  • 要包含反斜杠，需要写两个反斜杠(\\)
  • 可以使用\'来表示单引号

⚠️ 重要提示：默认情况下，standard_conforming_strings参数为on，这意味着只有E前缀的字符串才会识别转义序列。如果该参数设为off，所有字符串都会识别转义序列，但这种做法不推荐使用。

Unicode转义字符串 ​

要明确指定Unicode字符，可以使用Unicode转义语法，以U&开头（大写或小写）：

sql
-- 使用4位十六进制代码点
SELECT U&'d\0061t\0061';  -- 结果: data

-- 使用6位十六进制代码点(带+号)
SELECT U&'d\+000061t\+000061';  -- 结果: data

-- 俄语"слон"(大象)的示例
SELECT U&'\0441\043B\043E\043D';  -- 结果: слон

如果需要使用不同的转义字符替代反斜杠，可以使用UESCAPE子句：

sql
SELECT U&'d!0061t!0061' UESCAPE '!';  -- 结果: data

美元引号字符串 ​

对于包含多个单引号或特殊字符的复杂字符串，可以使用美元引号语法，这在编写函数或复杂SQL时特别有用：

sql
-- 基本美元引号字符串
SELECT $$ I don't need to escape single quotes! $$;

-- 带标签的美元引号字符串
SELECT $tag$字符串内容，可以包含'单引号'和"双引号"$tag$;

美元引号字符串的特点：

  • 内部字符都按字面意义处理，无需转义
  • 可以通过使用不同标签实现嵌套
  • 非常适合编写包含大量引号或特殊字符的内容

嵌套示例 ​

sql
CREATE FUNCTION check_pattern() RETURNS boolean AS
$function$
BEGIN
    RETURN ($1 ~ $pattern$[\t\r\n\\]$pattern$);
END;
$function$
LANGUAGE plpgsql;

位串常量 ​

位串常量用于表示二进制数据，有两种形式：

  1. 二进制形式：前缀为B(大写或小写)，仅包含0和1

    sql
    SELECT B'1010';  -- 二进制位串

  2. 十六进制形式：前缀为X(大写或小写)

    sql
    SELECT X'1F';  -- 十六进制位串(等同于B'00011111')

数值常量 ​

十进制形式 ​

数值常量可以是整数、小数或科学记数法：

sql
-- 整数
SELECT 42;

-- 小数
SELECT 3.14;
SELECT 4.;   -- 有效，等同于4.0
SELECT .001; -- 有效，等同于0.001

-- 科学记数法
SELECT 5e2;    -- 等同于500
SELECT 1.925e-3; -- 等同于0.001925

非十进制整数 ​

PostgreSQL也支持非十进制整数表示法：

sql
-- 十六进制(0x前缀)
SELECT 0x42F;   -- 十六进制整数(等同于1071)

-- 八进制(0o前缀)
SELECT 0o273;   -- 八进制整数(等同于187)

-- 二进制(0b前缀)
SELECT 0b10010; -- 二进制整数(等同于18)

数字分组 ​

为了提高可读性，可以在数字之间使用下划线作为视觉分隔符：

sql
SELECT 1_000_000;  -- 等同于1000000
SELECT 0b1000_0100;  -- 二进制数分组
SELECT 3.141_592;  -- 小数分组

📝 说明：下划线不会影响常量的值，只是为了提高可读性。

常量的类型转换 ​

可以使用多种方法将常量强制转换为指定类型：

sql
-- 方法1: type 'string'
SELECT REAL '1.23';

-- 方法2: ::type
SELECT 1.23::REAL;

-- 方法3: CAST
SELECT CAST('1.23' AS REAL);

-- 方法4: 函数风格
SELECT real('1.23');

小结 ​

常量是SQL的基础元素，PostgreSQL提供了多种灵活的表示方法：

  • 字符串常量用于文本数据，包括普通引号字符串、C风格转义、Unicode和美元引号等形式
  • 位串常量用于二进制数据
  • 数值常量支持多种数字形式和表示法

选择合适的常量表示形式可以让你的SQL查询更加清晰和有效。