可疑類型

他們的外表沒有任何可疑之處。 而且，你甚至覺得它們已經很熟悉了。 但這只是在你檢查它們之前。 這就是他們陰險本質的體現，其工作方式與你想像的完全不同。 有時他們會做出一些讓你毛骨悚然的事情 - 例如，他們丟失了委託給他們的秘密資料。 當你面對他們時，他們聲稱彼此不認識，儘管他們在同一幕幕下在暗處努力工作。 終於到了把它們帶到乾淨水源的時候了。 讓我們也處理這些可疑的類型。

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 函數。 但目前還不清楚這隻「陣貓」為何不重置陣法。 這種行為也只需要記住。

總結。 有很多奇怪的事情。 當然，他們中的大多數人並沒有批評到公然不恰當的行為。 其他的則透過易用性或在某些情況下的適用頻率來解釋。 但同時，也有很多驚喜。 因此，您需要了解它們。 如果您發現任何類型的行為有任何奇怪或不尋常的地方，請寫在評論中，我很樂意添加到它們可用的檔案中。

來源： www.habr.com