ประเภทที่น่าสงสัย

ไม่มีอะไรน่าสงสัยเกี่ยวกับรูปร่างหน้าตาของพวกเขา ยิ่งไปกว่านั้น พวกเขายังดูเหมือนคุ้นเคยกับคุณเป็นอย่างดีและเป็นเวลานานอีกด้วย แต่นั่นก็จนกว่าคุณจะตรวจสอบเท่านั้น นี่คือที่ที่พวกเขาแสดงธรรมชาติที่ร้ายกาจ โดยทำงานแตกต่างไปจากที่คุณคาดไว้อย่างสิ้นเชิง และบางครั้งพวกเขาก็ทำบางอย่างที่ทำให้ผมของคุณยืนหยัด เช่น พวกเขาสูญเสียข้อมูลลับที่ได้รับความไว้วางใจ เมื่อคุณเผชิญหน้ากับพวกเขา พวกเขาอ้างว่าพวกเขาไม่รู้จักกัน แม้ว่าจะอยู่ในเงามืดพวกเขาก็ทำงานหนักภายใต้หน้ากากเดียวกันก็ตาม ถึงเวลาที่ต้องนำพวกมันลงน้ำสะอาดในที่สุด ให้เราจัดการกับประเภทที่น่าสงสัยเหล่านี้ด้วย

การพิมพ์ข้อมูลใน 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

นี่คือ "ถ่าน" แบบไหน ตัวตลกแบบไหน? เราไม่ต้องการสิ่งเหล่านั้น... เพราะมันแกล้งทำเป็นถ่านธรรมดาถึงแม้ว่ามันจะอยู่ในเครื่องหมายคำพูดก็ตาม และมันแตกต่างจากถ่านทั่วไปซึ่งไม่มีเครื่องหมายคำพูดตรงที่มันจะส่งออกเฉพาะไบต์แรกของการแสดงสตริง ในขณะที่ถ่านปกติจะส่งออกอักขระตัวแรก ในกรณีของเรา อักขระตัวแรกคือตัวอักษร P ซึ่งในการแทนค่า Unicode จะใช้พื้นที่ 2 ไบต์ ซึ่งเห็นได้จากการแปลงผลลัพธ์เป็นประเภท bytea และประเภท "char" ใช้เพียงไบต์แรกของการแสดงยูนิโค้ดนี้ แล้วเหตุใดจึงต้องมีประเภทนี้? เอกสาร 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 ในเวลาเดียวกัน ตัววัตถุ JSON เองอาจมีค่าเป็น NULL ดังนั้นนิพจน์ SELECT null::jsonb IS NULL (โปรดสังเกตว่าไม่มีเครื่องหมายคำพูดเดี่ยว) จะคืนค่าเป็นจริงในครั้งนี้

จดหมายฉบับเดียวเปลี่ยนแปลงทุกสิ่ง

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 ซึ่งทำให้เธอทึ่ง โดยทั่วไปแล้ว เมื่อส่งวันที่ในรูปแบบข้อความ คุณจะต้องตรวจสอบอย่างรอบคอบว่าฐานข้อมูลจดจำข้อมูลเหล่านั้นได้อย่างถูกต้องเพียงใด (โดยเฉพาะอย่างยิ่ง วิเคราะห์พารามิเตอร์ datestyle ด้วยคำสั่ง SHOW 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

เกิดอะไรขึ้น? ความจริงก็คือในการแปลงประเภทการประทับเวลาเป็นประเภทวันที่ จะใช้ค่าของพารามิเตอร์ระบบ 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 โดยปริยาย แต่ก็ยังไม่มีความชัดเจนว่าทำไม "array cat" นี้จึงไม่รีเซ็ตอาเรย์ พฤติกรรมนี้ก็ต้องจดจำเช่นกัน

สรุป. มีของแปลกมากมาย แน่นอนว่าส่วนใหญ่ไม่ได้มีความสำคัญอย่างยิ่งในการพูดถึงพฤติกรรมที่ไม่เหมาะสมอย่างโจ่งแจ้ง และอื่นๆ อธิบายได้จากความง่ายในการใช้งานหรือความถี่ของการนำไปใช้ในบางสถานการณ์ แต่ในขณะเดียวกันก็มีเรื่องเซอร์ไพรส์มากมาย ดังนั้นคุณจำเป็นต้องรู้เกี่ยวกับพวกเขา หากคุณพบสิ่งอื่นที่แปลกหรือผิดปกติในพฤติกรรมทุกประเภท โปรดเขียนความคิดเห็น ฉันยินดีที่จะเพิ่มลงในเอกสารที่มีอยู่

ที่มา: will.com