可疑类型

他们的外表没有任何可疑之处。 而且，你甚至觉得它们已经很熟悉了。 但这只是在你检查它们之前。 这就是他们阴险本质的体现，其工作方式与你想象的完全不同。 有时他们会做出一些让你毛骨悚然的事情 - 例如，他们丢失了委托给他们的秘密数据。 当你面对他们时，他们声称彼此不认识，尽管他们在同一幕幕下在暗处努力工作。 终于到了把它们带到干净水源的时候了。 让我们也处理这些可疑的类型。

PostgreSQL 中的数据类型，尽管有其所有逻辑，有时确实会带来非常奇怪的惊喜。 在这篇文章中，我们将尝试澄清他们的一些怪癖，了解他们奇怪行为的原因，并了解如何在日常实践中避免遇到问题。 说实话，我编这篇文章也是为了给自己写一本参考书，一本在有争议的案件中可以方便参考的参考书。 因此，当发现可疑类型的新惊喜时，将会对其进行补充。 那么，我们走吧，不知疲倦的数据库追踪者！

档案第一。 实数/双精度/数字/货币

就行为意外而言，数字类型似乎是问题最少的。 但不管怎样。 那么让我们从他们开始吧。 所以…

忘记怎么数了

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

怎么了？ 问题在于 PostgreSQL 将无类型常量 0.1 转换为双精度，并尝试将其与实数类型 0.1 进行比较。 而且这些是完全不同的含义！ 这个想法是在机器内存中表示实数。 由于0.1不能表示为有限的二进制分数（二进制为0.0（0011）），因此不同位深的数字会不同，因此结果不相等。 一般来说，这是一个单独的文章的主题；我不会在这里写得更详细。

错误从何而来？

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24

许多人都知道 PostgreSQL 允许使用函数符号进行类型转换。 也就是说，你不仅可以写1::int，还可以写int(1)，这将是等价的。 但不适用于名称由多个单词组成的类型！ 因此，如果要将数值转换为函数形式的双精度类型，请使用该类型 float8 的别名，即 SELECT float8(1)。

什么比无穷大更大？

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

看看它是什么样子的！ 事实证明，有一个比无穷大更大的东西，它就是 NaN！ 与此同时，PostgreSQL 文档以诚实的眼光看待我们，并声称 NaN 明显大于任何其他数字，因此是无穷大。 对于 -NaN 也成立相反的情况。 你好，数学爱好者！ 但我们必须记住，所有这些都是在实数的背景下进行的。

眼睛变圆

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

来自基地的又一个意想不到的问候。 再次请记住，双精度和数字类型具有不同的舍入效果。 对于数字 - 通常的方式，当 0,5 向上舍入时，对于双精度 - 0,5 向最接近的偶数舍入。

钱是很特别的东西

SELECT '10'::money::float8

ERROR:  cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19

根据 PostgreSQL 的说法，金钱不是一个真实的数字。 据一些人的说法也是如此。 我们需要记住，货币类型只能转换为数字类型，就像只有数字类型可以转换为货币类型一样。 但现在你可以随心所欲地玩它。 但不会是同样的钱。

Smallint 和序列生成

SELECT *
  FROM generate_series(1::smallint, 5::smallint, 1::smallint)

ERROR:  function generate_series(smallint, smallint, smallint) is not unique
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Ошибка **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-состояние: 42725
Подсказка: Could not choose a best candidate function. You might need to add explicit type casts.
Символ: 18

PostgreSQL 不喜欢在琐事上浪费时间。 这些基于smallint的序列是什么？ int，不能少！ 因此，当尝试执行上述查询时，数据库尝试将smallint 转换为其他整数类型，并发现可能存在多个此类转换。 选择哪个演员？ 她无法决定这一点，因此因错误而崩溃。

文件二号。 “字符”/字符/varchar/文本

角色类型中也存在许多奇怪的地方。 我们也来认识一下他们吧。

这些到底是什么伎俩呢？

SELECT 'ПЕТЯ'::"char"
     , 'ПЕТЯ'::"char"::bytea
     , 'ПЕТЯ'::char
     , 'ПЕТЯ'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 ╨     | xd0  | П            | xd09f

这是什么类型的“角色”，这是什么样的小丑？ 我们不需要那些......因为它假装是一个普通的字符，即使它在引号中。 它与不带引号的常规 char 不同，它仅输出字符串表示形式的第一个字节，而普通 char 输出第一个字符。 在我们的例子中，第一个字符是字母 P，在 unicode 表示中占用 2 个字节，通过将结果转换为 bytea 类型即可证明这一点。 而“char”类型仅采用此 unicode 表示形式的第一个字节。 那为什么需要这个类型呢？ PostgreSQL 文档说这是用于特殊需求的特殊类型。 所以我们不太可能需要它。 但当你看到他的特殊行为时，看着他的眼睛，你就不会认错。

额外的空间。 眼不见，心不烦

SELECT 'abc   '::char(6)::bytea
     , 'abc   '::char(6)::varchar(6)::bytea
     , 'abc   '::varchar(6)::bytea

     bytea     |   bytea  |     bytea
     bytea     |   bytea  |     bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020

看一下给出的例子。 我特地将所有结果都转换为bytea类型，这样就可以清楚地看到那里有什么。 转换为 varchar(6) 后尾随空格在哪里？ 该文档简洁地指出：“将字符的值转换为另一种字符类型时，尾随空白将被丢弃。” 这种厌恶必须被记住。 请注意，如果将带引号的字符串常量直接转换为 varchar(6) 类型，则会保留尾随空格。 这就是奇迹。

文件号三。 json/jsonb

JSON 是一个独立的结构，有自己的生命。 因此，它的实体和PostgreSQL的实体略有不同。 以下是示例。

约翰逊和约翰逊。 感到不同

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

问题是 JSON 有自己的 null 实体，这与 PostgreSQL 中的 NULL 不同。 同时，JSON 对象本身很可能具有 NULL 值，因此表达式 SELECT null::jsonb IS NULL （注意没有单引号）这次将返回 true。

一封信改变一切

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json

                     json
                     json
------------------------------------------------
{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}

---

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb

             jsonb
             jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}

问题是 json 和 jsonb 是完全不同的结构。 在 json 中，对象按原样存储，而在 jsonb 中，它已经以解析的索引结构的形式存储。 这就是为什么在第二种情况下，键 1 的对象的值从 [1, 2, 3] 替换为 [7, 8, 9]，它在最后以相同的键进入结构。

不要喝脸上的水

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL 在其 JSONB 实现中改变了实数的格式，使它们成为经典形式。 JSON 类型不会发生这种情况。 有点奇怪，但他是对的。

文件四号。 日期/时间/时间戳

日期/时间类型也有一些奇怪的地方。 让我们看看它们。 让我立即保留一点，如果您很好地理解了时区工作的本质，那么一些行为特征就会变得清晰。 但这也是另一篇文章的主题。

我不明白你的

SELECT '08-Jan-99'::date

ERROR:  date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.
********** Ошибка **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-состояние: 22008
Подсказка: Perhaps you need a different "datestyle" setting.
Символ: 8

似乎这里有什么难以理解的地方？ 但数据库仍然不明白我们把什么放在第一位——年份还是日期？ 她决定日期是 99 年 2008 月 XNUMX 日，这让她大吃一惊。 一般来说，当以文本格式传输日期时，您需要非常仔细地检查数据库识别它们的正确程度（特别是使用 SHOW datestyle 命令分析 datestyle 参数），因为这方面的歧义可能会非常昂贵。

你是哪里人？

SELECT '04:05 Europe/Moscow'::time

ERROR:  invalid input syntax for type time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
               ^
********** Ошибка **********
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
SQL-состояние: 22007
Символ: 8

为什么数据库无法理解明确指定的时间？ 因为时区没有缩写，而是有全名，只有在日期上下文中才有意义，因为它考虑了时区更改的历史记录，并且没有日期就不起作用。 时间线的措辞本身就引发了疑问——程序员的真正意思是什么？ 因此，如果你仔细观察的话，这里的一切都是合乎逻辑的。

他究竟怎么了？

想象一下情况。 您的表中有一个类型为 timestamptz 的字段。 你想要索引它。 但您知道，由于其高选择性（几乎所有这种类型的值都是唯一的），因此在此字段上构建索引并不总是合理的。 因此，您决定通过将类型转换为日期来降低索引的选择性。 然后你会得到一个惊喜：

CREATE INDEX "iIdent-DateLastUpdate"
  ON public."Ident" USING btree
  (("DTLastUpdate"::date));

ERROR:  functions in index expression must be marked IMMUTABLE
********** Ошибка **********
ERROR: functions in index expression must be marked IMMUTABLE
SQL-состояние: 42P17

怎么了？ 事实上，要将 timestamptz 类型转换为日期类型，需要使用 TimeZone 系统参数的值，这使得类型转换函数依赖于自定义参数，即易挥发的。 索引中不允许使用此类函数。 在这种情况下，您必须明确指示在哪个时区执行类型转换。

当现在根本就不是现在

我们习惯于 now() 返回当前日期/时间，并考虑到时区。 但请看以下查询：

START TRANSACTION;
SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

COMMIT;

无论自上次请求以来已经过去了多少时间，日期/时间都会返回相同的值！ 怎么了？ 事实上，now()并不是当前时间，而是当前事务的开始时间。 因此，它在交易内不会改变。 在事务范围之外启动的任何查询都会隐式包装在事务中，这就是为什么我们没有注意到简单的 SELECT now(); 返回的时间； 事实上，不是当前时间...如果你想获得诚实的当前时间，你需要使用clock_timestamp()函数。

档案五号。 少量

有点奇怪

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

如果进行类型扩展，应该在哪一侧添加位？ 好像是在左边。 但只有基地对此事有不同看法。 请注意：如果转换类型时位数不匹配，您将无法得到您想要的结果。 这适用于向右添加位和修剪位。 还有右边...

第六号文件。 数组

即使 NULL 也没有触发

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

正如普通人在 SQL 中长大的那样，我们期望这个表达式的结果为 NULL。 但它不在那里。 返回一个数组。 为什么？ 因为在这种情况下，基类将 NULL 转换为整数数组并隐式调用 array_cat 函数。 但目前还不清楚这只“阵猫”为何不重置阵法。 这种行为也只需要记住。

总结。 有很多奇怪的事情。 当然，他们中的大多数人并没有批评到公然不恰当的行为。 其他的则通过易用性或在某些情况下的适用频率来解释。 但与此同时，也有很多惊喜。 因此，您需要了解它们。 如果您发现任何类型的行为有任何奇怪或不寻常的地方，请写在评论中，我很乐意添加到它们可用的档案中。

来源： habr.com