🥇疑わしいタイプ | プロホスター

彼らの外見には何の疑いもありません。さらに、それらはあなたにとって、長い間よく知られているようにさえ思えます。ただし、それは確認するまでの話です。ここで彼らの陰湿な本性が現れ、予想とはまったく異なる動作をします。そして時には、彼らは髪の毛が逆立つようなことをすることがあります。たとえば、彼らに託された機密データが失われるなどです。彼らに対峙すると、彼らはお互いのことを知らないと主張しますが、影では同じフードの下で熱心に働いています。ついに彼らをきれいな水に連れて行く時が来ました。こうした不審なタイプにも対処しましょう。

PostgreSQL でのデータの型付けは、そのロジック全体から見て、非常に奇妙な驚きをもたらすことがあります。この記事では、彼らの癖のいくつかを明らかにし、彼らの奇妙な行動の理由を理解し、日常生活で問題に遭遇しない方法を理解しようとします。実を言うと、この記事は私自身のための一種の参考書、論争の際に参照しやすい参考書としてまとめたものでもあります。したがって、疑わしいタイプからの新しい驚きが発見されると、補充されます。それでは、行きましょう、おお疲れ知らずのデータベース追跡者たち!

書類その１。実数/倍精度/数値/通貨

動作の意外性という点では、数値型が最も問題が少ないように思えます。しかし、それがどのようなものであっても。それでは、それらから始めましょう。それで…

数え方を忘れた

SELECT 0.1::real = 0.1

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

どうしたの？問題は、PostgreSQL が型なし定数 0.1 を倍精度に変換し、それを実数型の 0.1 と比較しようとすることです。そしてこれらはまったく異なる意味です！アイデアは、マシンのメモリ内で実数を表現することです。 0.1 は有限の 0.0 進数の小数として表すことができないため (0011 進数では XNUMX(XNUMX) になります)、桁が異なる数値は異なるため、結果は等しくなくなります。一般的に、これは別の記事のトピックなので、ここでは詳しく書きません。

エラーはどこから来たのでしょうか?

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 を他の整数型にキャストしようとし、そのようなキャストがいくつかある可能性があることを確認します。どのキャストを選ぶか？彼女はこれを決定できず、エラーでクラッシュします。

ファイル番号 XNUMX。 "char"/char/varchar/text

文字タイプにも多くの奇妙な点があります。彼らについても知ってみましょう。

これらはどのようなトリックですか？

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

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

この「イワナ」は何の種類ですか、これは何のピエロですか？これらは必要ありません...引用符で囲まれているにもかかわらず、通常の char であるかのように見せかけているためです。また、通常の char は最初の文字を出力するのに対し、文字列表現の最初のバイトのみを出力するという点で、引用符のない通常の char とは異なります。この場合、最初の文字は文字 P であり、結果を bytea 型に変換するとわかるように、Unicode 表現では 2 バイトを占めます。そして、「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) にキャストした後の末尾のスペースはどこにありますか? ドキュメントには、「character の値を別の文字型にキャストする場合、末尾の空白は破棄されます。」と簡潔に記載されています。この嫌悪感は忘れてはならない。また、引用符で囲まれた文字列定数が varchar(6) 型に直接キャストされる場合、末尾のスペースは保持されることに注意してください。それが奇跡なのです。

ファイル番号 XNUMX。 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, XNUMX] に置き換えられ、同じキーで構造体の最後に追加されました。

顔についた水を飲まないでください

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 タイプではこれは発生しません。少し奇妙ですが、彼は正しいです。

ファイル番号 XNUMX。日付/時刻/タイムスタンプ

日付/時刻型にも奇妙な点がいくつかあります。それらを見てみましょう。タイムゾーンを扱うことの本質をよく理解すれば、行動の特徴のいくつかが明らかになるということをすぐに予約させてください。しかし、これも別の記事で取り上げます。

私はあなたのことを理解していません

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 型を date 型にキャストするには、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() 関数を使用する必要があります。

ファイル番号 XNUMX。少し

少し奇妙です

SELECT '111'::bit(4)

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

型拡張の場合はどちら側にビットを追加すればよいでしょうか？左側にあるようです。しかし、この件に関しては基地だけが異なる意見を持っている。注意: 型をキャストするときに桁数が一致しないと、期待した結果が得られません。これは、右側へのビットの追加とビットのトリミングの両方に当てはまります。右側にも…

ファイル番号 XNUMX。配列

NULLでも発火しなかった

SELECT ARRAY[1, 2] || NULL

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

SQL で育った普通の人々として、私たちはこの式の結果が NULL であることを期待します。しかし、そこにはありませんでした。配列が返されます。なぜ？この場合、ベースは NULL を整数配列にキャストし、暗黙的に array_cat 関数を呼び出すためです。しかし、なぜこの「配列猫」が配列をリセットしないのかはまだ不明です。この動作も覚えておく必要があります。

要約します。不思議なことはたくさんあります。もちろん、それらのほとんどは、あからさまに不適切な行為について語るほど批判的なものではありません。また、使いやすさや特定の状況での適用頻度によって説明されるものもあります。しかし同時に、多くの驚きもあります。したがって、それらについて知る必要があります。任意のタイプの動作で他に奇妙または異常な点を見つけた場合は、コメントに書き込んでください。入手可能な資料に喜んで追加します。

出所： habr.com

疑わしいタイプ

書類その１。 実数/倍精度/数値/通貨