의심스러운 유형

그들의 외모에는 의심스러운 것이 없습니다. 더욱이 그들은 당신에게 오랫동안 친숙해 보입니다. 하지만 그건 확인하기 전까지만 가능합니다. 이곳은 그들이 예상했던 것과는 완전히 다르게 작동하는 교활한 본성을 보여주는 곳입니다. 그리고 때때로 그들은 당신의 머리카락을 곤두서게 만드는 일을 합니다. 예를 들어, 그들은 그들에게 맡겨진 비밀 데이터를 잃어버립니다. 당신이 그들과 마주했을 때, 그들은 서로를 모른다고 주장하지만, 그림자 속에서 그들은 같은 후드 아래에서 부지런히 일합니다. 마침내 깨끗한 물로 가져올 시간입니다. 이러한 의심스러운 유형도 처리해 보겠습니다.

PostgreSQL의 모든 논리에 대한 데이터 입력은 때때로 매우 이상한 놀라움을 선사합니다. 이 글에서 우리는 그들의 특이한 점을 명확히 하고, 그들의 이상한 행동의 이유를 이해하고, 일상 생활에서 문제에 부딪치지 않는 방법을 이해하려고 노력할 것입니다. 사실을 말하자면, 나는 이 글을 나 자신을 위한 일종의 참고서, 논란이 되는 사건에서 쉽게 참고할 수 있는 참고서로도 작성했다. 따라서 수상한 유형의 새로운 놀라움이 발견되면 보충됩니다. 자, 가자, 지칠 줄 모르는 데이터베이스 추적기들이여!

서류 XNUMX번. 실수/배정밀도/숫자/돈

숫자 유형은 행동의 놀라움 측면에서 문제가 가장 적은 것 같습니다. 그러나 그것이 어떻든 상관 없습니다. 그럼 그들부터 시작해 보겠습니다. 그래서…

계산하는 방법을 잊어버렸어요

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를 다른 정수 유형으로 캐스팅하려고 시도하며 이러한 캐스팅이 여러 개 있을 수 있음을 확인합니다. 어떤 캐스트를 선택할 것인가? 그녀는 이를 결정할 수 없으므로 오류가 발생하여 충돌이 발생합니다.

파일 번호 XNUMX. "문자"/char/varchar/텍스트

문자 유형에도 여러 가지 이상한 점이 있습니다. 그들도 알아봅시다.

이것은 어떤 트릭입니까?

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

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

이것은 어떤 종류의 "char"이고, 이것은 어떤 종류의 광대입니까? 필요하지 않습니다... 따옴표로 묶어도 일반 문자인 것처럼 가장하기 때문입니다. 그리고 일반 char은 문자열 표현의 첫 번째 바이트만 출력하는 반면 일반 char은 첫 번째 문자를 출력한다는 점에서 따옴표가 없는 일반 char와 다릅니다. 우리의 경우 첫 번째 문자는 문자 P입니다. 이는 결과를 bytea 유형으로 변환하여 알 수 있듯이 유니코드 표현에서 2바이트를 차지합니다. 그리고 "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이 PostgreSQL의 NULL과 유사하지 않은 고유한 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}

JSONB 구현의 PostgreSQL은 실수의 형식을 변경하여 이를 고전적인 형식으로 만듭니다. 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 유형을 날짜 유형으로 변환하기 위해 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 함수를 호출하기 때문입니다. 그러나 이 "array cat"이 왜 어레이를 재설정하지 않는지는 여전히 불분명합니다. 이 행동도 기억하면 됩니다.

요약하다. 이상한 것들이 많이 있습니다. 물론 그들 중 대부분은 노골적으로 부적절한 행동에 대해 이야기할 만큼 비판적이지 않습니다. 그리고 다른 것들은 사용의 용이성이나 특정 상황에서의 적용 빈도로 설명됩니다. 그러나 동시에 놀라운 일도 많이 있습니다. 그러므로 그들에 대해 알아야합니다. 어떤 유형의 동작에서 이상하거나 특이한 점을 발견하면 의견을 적어주세요. 해당 문서에 기꺼이 추가해 드리겠습니다.

출처 : habr.com