PostgreSQL-运算符和函数3

一、数据类型格式化函数

在 to_char 输出模板字符串中，某些模式被识别并替换为基于给定值的适当格式的数据。任何不是模板模式的文本都被简单地逐字复制。类似地，在输入模板字符串（对于其他函数）中，模板模式标识要由输入数据字符串提供的值。如果模板字符串中存在非模板模式的字符，则直接跳过输入数据字符串中的相应字符（无论它们是否等于模板字符串字符）。

修饰符可以应用于任何模板模式以改变其行为。例如，FMMonth 是带有 FM 修饰符的月份模式。

说明：

FM 抑制了前导零和尾随空白，否则它们会被添加以使模式的输出为固定宽度。在 PostgreSQL 中，FM 仅修改下一个规范，而在 Oracle FM 中会影响所有后续规范，并且重复的 FM 修改器会打开和关闭填充模式。

无论是否指定了 FM，TM 都会抑制尾随空白。

to_timestamp 和 to_date 忽略输入中的字母大小写；例如 MON、Mon 和 mon 都接受相同的字符串。使用 TM 修饰符时，根据函数输入排序规则的规则进行大小写折叠。

除非使用 FX 选项，否则 to_timestamp 和 to_date 在输入字符串的开头以及日期和时间值周围跳过多个空格。例如，to_timestamp(‘2000 JUN’, ‘YYYY MON’) 和 to_timestamp(‘2000 – JUN’, ‘YYYY-MON’) 有效，但 to_timestamp(‘2000 JUN’, ‘FXYYYY MON’) 会返回错误，因为 to_timestamp只需要一个空格。FX 必须指定为模板中的第一项。

to_timestamp 和 to_date 的模板字符串中的分隔符（空格或非字母/非数字字符）与输入字符串中的任何单个分隔符匹配或被跳过，除非使用了 FX 选项。例如， to_timestamp(‘2000JUN’, ‘YYYY///MON’) 和 to_timestamp(‘2000/JUN’, ‘YYYY MON’) 有效，但 to_timestamp(‘2000//JUN’, ‘YYYY/MON’) 返回一个错误，因为输入字符串中的分隔符数量超过了模板中的分隔符数量。

如果指定了 FX，则模板字符串中的分隔符与输入字符串中的一个字符完全匹配。但请注意，输入字符串字符不需要与模板字符串的分隔符相同。例如，to_timestamp(‘2000/JUN’, ‘FXYYYY MON’) 有效，但 to_timestamp(‘2000/JUN’, ‘FXYYYY  MON’) 会返回错误，因为模板字符串中的第二个空格占用了输入中的字母 J。

TZH 模板模式可以匹配带符号的数字。如果没有 FX 选项，减号可能不明确，并且可能被解释为分隔符。这种歧义解决如下：如果模板字符串中 TZH 之前的分隔符数量小于输入字符串中减号之前的分隔符数量，则将减号解释为 TZH 的一部分。否则，减号被认为是值之间的分隔符。例如，to_timestamp(‘2000 -10’, ‘YYYY TZH’) 将 -10 匹配到 TZH，但 to_timestamp(‘2000 -10’, ‘YYYY  TZH’) 将 10 匹配到 TZH。

to_char 模板中允许使用普通文本，并将按字面意思输出。您可以将子字符串放在双引号中以强制将其解释为文字文本，即使它包含模板模式。例如，'”Hello Year”YYYY’ 中，YYYY 会被替换为年份数据，但 Year 中的单个 Y 不会。在 to_date、to_number 和 to_timestamp 中，文字文本和双引号字符串会导致跳过字符串中包含的字符数；例如“XX”跳过两个输入字符（无论它们是否是 XX）。

如果要在输出中使用双引号，则必须在其前面加上反斜杠，例如 ‘”YYYY Month”‘。反斜杠在双引号字符串之外没有其他特殊之处。在双引号字符串中，反斜杠会导致下一个字符按字面意思表示，无论它是什么（但这没有特殊效果，除非下一个字符是双引号或另一个反斜杠）。

在 to_timestamp 和 to_date 中，如果年份格式规范小于四位，例如 YYY，并且提供的年份小于四位，则年份将调整为最接近 2020 年，例如，95 变为 1995。

在 to_timestamp 和 to_date 中，负年份被视为表示 BC。如果你同时写了一个负年份和一个明确的 BC 字段，你会再次得到 AD。零年的输入被视为 1 BC。

在 to_timestamp 和 to_date 中，YYYY 转换在处理超过 4 位数的年份时有限制。您必须在 YYYY 之后使用一些非数字字符或模板，否则年份总是被解释为 4 位数字。例如（年份为 20000）： to_date(‘200001131’, ‘YYYYMMDD’) 将被解释为 4 位数的年份；而是在年份之后使用非数字分隔符，例如 to_date(‘20000-1131’, ‘YYYY-MMDD’) 或 to_date(‘20000Nov31’, ‘YYYYMonDD’)。

在 to_timestamp 和 to_date 中，接受 CC（世纪）字段，但如果存在 YYY、YYYY 或 Y,YYY 字段，则忽略该字段。如果 CC 与 YY 或 Y 一起使用，则结果计算为指定世纪中的那一年。如果指定了世纪但未指定年份，则假定为世纪的第一年。

在 to_timestamp 和 to_date 中，可以通过以下两种方式之一指定 ISO 8601 周编号日期（不同于公历日期）：

*年、周数和工作日：例如 to_date(‘2006-42-4’, ‘IYYY-IW-ID’) 返回日期 2006-10-19。如果省略工作日，则假定为 1（星期一）。
*年份和日期：例如 to_date(‘2006-291’, ‘IYYY-IDDD’) 也返回 2006-10-19。

尝试使用 ISO 8601 周编号字段和公历日期字段的混合输入日期是荒谬的，并且会导致错误。在 ISO 8601 周编号年份的上下文中，“月”或“月中的某天”的概念没有任何意义。在公历年的背景下，ISO 周没有任何意义。

在 to_timestamp 中，毫秒 (MS) 或微秒 (US) 字段用作小数点后的秒数。例如 to_timestamp(‘12.3’, ‘SS.MS’) 不是 3 毫秒，而是 300，因为转换将其视为 12 + 0.3 秒。因此，对于 SS.MS 格式，输入值 12.3、12.30 和 12.300 指定相同的毫秒数。要获得三毫秒，必须写入 12.003，转换将其视为 12 + 0.003 = 12.003 秒。

这是一个更复杂的示例： to_timestamp(’15:12:02.020.001230′, ‘HH24:MI:SS.MS.US’) 是 15 小时 12 分 2 秒 + 20 毫秒 + 1230 微秒 = 2.021230 秒.

to_char(…, ‘IS’)’ 一周中的某一天编号匹配 extract(isodow from …) 函数，但 to_char(…, ‘D’)’s 不匹配 extract(dow from …) 的日期编号。

to_char(interval) 将 HH 和 HH12 格式化为 12 小时制，例如，0 小时和 36 小时都输出为 12，而 HH24 输出完整小时值，间隔值可以超过 23。

数字格式的模板模式:

说明：

0 指定将始终打印的数字位置，即使它包含前导/尾随零。9 也指定了一个数字位置，但如果它是前导零，那么它将被一个空格替换，而如果它是一个尾随零并且指定了填充模式，那么它将被删除。（对于 to_number()，这两个模式字符是等价的。）

模式字符 S、L、D 和 G 表示由当前语言环境定义的符号、货币符号、小数点和千位分隔符。模式字符句点和逗号代表那些精确的字符，具有小数点和千位分隔符的含义，与语言环境无关。

如果在 to_char() 的模式中没有明确规定符号，则将为符号保留一列，并将锚定到数字（出现在数字的左侧）。如果 S 出现在一些 9 的左边，它同样会被锚定到数字上。

使用 SG、PL 或 MI 格式化的符号不固定在数字上；例如， to_char(-12, ‘MI9999’) 产生 ‘- 12’ 但 to_char(-12, ‘S9999’) 产生 ‘  -12’。

TH 不转换小于零的值，也不转换小数。PL、SG 和 TH 是 PostgreSQL 扩展。

在to_number中，如果使用了L或TH等非数据模板模式，则无论是否匹配模板模式，都会跳过相应数量的输入字符，除非是数据字符（即数字、符号、小数点），或逗号）。例如，TH 会跳过两个非数据字符。

带有 to_char 的 V 将输入值乘以 10^n，其中 n 是 V 之后的位数。to_char 和 to_number 不支持将 V 与小数点结合使用（例如，不允许使用 99.9V99）。

EEEE（科学记数法）不能与除数字和小数点模式之外的任何其他格式模式或修饰符组合使用，并且必须位于格式字符串的末尾（例如，9.99EEEE 是有效模式）。

用于数字格式的模板模式修饰符：

例子：

select '#'||to_char(current_timestamp, 'Day, DD  HH12:MI:SS')||'#';
select '#'||to_char(current_timestamp, 'FMDay, FMDD  HH12:MI:SS')||'#';
select '#'||to_char(-0.1, '99.99')||'#';
select '#'||to_char(-0.1, 'FM9.99')||'#';
select '#'||to_char(-0.1, 'FM90.99')||'#';
select '#'||to_char(0.1, '0.9')||'#';

select '#'||to_char(12, '9990999.9')||'#';
select '#'||to_char(12, 'FM9990999.9')||'#';
select '#'||to_char(485, '999')||'#';
select '#'||to_char(-485, '999')||'#';

select '#'||to_char(485, '9 9 9')||'#';
select '#'||to_char(1485, '9,999')||'#';
select '#'||to_char(1485, '9G999')||'#';
select '#'||to_char(148.5, '999.999')||'#';
select '#'||to_char(148.5, 'FM999.999')||'#';

select '#'||to_char(148.5, 'FM999.990')||'#';
select '#'||to_char(148.5, '999D999')||'#';
select '#'||to_char(3148.5, '9G999D999')||'#';
select '#'||to_char(-485, '999S')||'#';
select '#'||to_char(-485, '999MI')||'#';

select '#'||to_char(485, '999MI')||'#';
select '#'||to_char(485, 'FM999MI')||'#';
select '#'||to_char(485, 'PL999')||'#';
select '#'||to_char(485, 'SG999')||'#';
select '#'||to_char(-485, 'SG999')||'#';

select '#'||to_char(-485, '9SG99')||'#';
select '#'||to_char(-485, '999PR')||'#';
select '#'||to_char(485, 'L999')||'#';
select '#'||to_char(485, 'RN')||'#';
select '#'||to_char(485, 'FMRN')||'#';
select '#'||to_char(5.2, 'FMRN')||'#';

select '#'||to_char(482, '999th')||'#';
select '#'||to_char(485, '"Good number:"999')||'#';
select '#'||to_char(485.8, '"Pre:"999" Post:" .999')||'#';
select '#'||to_char(12, '99V999')||'#';
select '#'||to_char(12.4, '99V999')||'#';
select '#'||to_char(12.45, '99V9')||'#';
select '#'||to_char(0.0004859, '9.99EEEE')||'#';

二、日期/时间函数和运算符

EXTRACT(field FROM source)

extract 函数从日期/时间值中检索子字段，例如年或小时。source 必须是时间戳、时间或间隔类型的值表达式。（日期类型的表达式转换为时间戳，因此也可以使用。）字段是一个标识符或字符串，用于选择从源值中提取的字段。extract 函数返回数值类型的值。

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');

一年中 ISO 8601 周编号的周数。根据定义，ISO 周从星期一开始，一年的第一周包含当年的 1 月 4 日。换句话说，一年中的第一个星期四是在该年的第一周。

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');


date_part('field', source)

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');

date_part与EXTRACT功能相同，但是date_part返回float8，可能会产生精度问题，建议用EXTRACT。

PostgreSQL 提供了许多返回与当前日期和时间相关的值的函数。这些 SQL 标准函数都基于当前事务的开始时间返回值：

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

CURRENT_TIME 和 CURRENT_TIMESTAMP 提供带有时区的值；LOCALTIME 和 LOCALTIMESTAMP 提供没有时区的值。由于这些函数返回当前事务的开始时间，因此它们的值在事务期间不会改变。这被认为是一个特性：目的是允许单个事务具有一致的“当前”时间概念，以便同一事务中的多个修改具有相同的时间戳。

PostgreSQL 还提供了返回当前语句开始时间的函数，以及调用函数时的实际当前时间。非 SQL 标准时间函数的完整列表是：

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

transaction_timestamp() 等价于 CURRENT_TIMESTAMP，但被命名为清楚地反映它返回的内容。statement_timestamp() 返回当前语句的开始时间（更具体地说，是从客户端收到最新命令消息的时间）。statement_timestamp() 和 transaction_timestamp() 在事务的第一个命令期间返回相同的值，但在后续命令期间可能会有所不同。clock_timestamp() 返回实际的当前时间，因此即使在单个 SQL 命令中其值也会发生变化。timeofday() 是一个历史 PostgreSQL 函数。与clock_timestamp() 一样，它返回实际的当前时间，但作为格式化的文本字符串而不是带有时区值的时间戳。now() 是传统的 PostgreSQL 等价于 transaction_timestamp()。

延迟执行：

以下函数可用于延迟服务器进程的执行：

pg_sleep ( double precision )
pg_sleep_for ( interval )
pg_sleep_until ( timestamp with time zone )

pg_sleep 使当前会话的进程休眠，直到经过给定的秒数。可以指定小数秒延迟。pg_sleep_for 是一个方便的函数，允许将睡眠时间指定为间隔。pg_sleep_until 是一个方便的函数，用于需要特定的唤醒时间。例如：

SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');

pg_sleep_until 不能保证准确地在指定的时间唤醒，但它不会更早地唤醒。

确保您的会话在调用 pg_sleep 或其变体时不会持有过多的锁。否则其他会话可能不得不等待您的睡眠过程，从而减慢整个系统的速度。

三、枚举支持函数

CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple');

![](https://www.icode9.com/i/l/?n=22&i=blog/2375787/202209/2375787-20220910105300967-786556495.png)

select enum_first(null::rainbow);
select enum_last(null::rainbow);
select enum_range(null::rainbow);
select enum_range('orange'::rainbow, 'green'::rainbow);
select enum_range(NULL, 'green'::rainbow);
select enum_range('orange'::rainbow, NULL);
select enum_range('blue'::rainbow,'red'::rainbow);