🥇Tipe mencurigakan

Tidak ada yang mencurigakan dari penampilan mereka. Selain itu, mereka bahkan tampak akrab bagi Anda untuk waktu yang lama. Tapi itu hanya sampai Anda memeriksanya. Di sinilah mereka menunjukkan sifat berbahaya mereka, bekerja dengan cara yang sangat berbeda dari yang Anda harapkan. Dan terkadang mereka melakukan sesuatu yang membuat Anda merinding - misalnya, mereka kehilangan data rahasia yang dipercayakan kepada mereka. Ketika Anda menghadapi mereka, mereka menyatakan bahwa mereka tidak mengenal satu sama lain, meskipun dalam bayang-bayang mereka bekerja keras di bawah tenda yang sama. Saatnya akhirnya membawa mereka ke air bersih. Mari kita juga menangani jenis-jenis mencurigakan ini.

Pengetikan data di PostgreSQL, dengan segala logikanya, terkadang menghadirkan kejutan yang sangat aneh. Pada artikel ini kami akan mencoba menjelaskan beberapa kebiasaan mereka, memahami alasan perilaku aneh mereka dan memahami bagaimana agar tidak mengalami masalah dalam praktik sehari-hari. Sejujurnya, artikel ini saya susun juga sebagai semacam buku referensi bagi diri saya sendiri, sebuah buku referensi yang dapat dengan mudah dijadikan acuan dalam kasus-kasus kontroversial. Oleh karena itu, ini akan diisi ulang ketika kejutan baru dari jenis yang mencurigakan ditemukan. Jadi, ayo berangkat, oh pelacak basis data yang tak kenal lelah!

Berkas nomor satu. nyata/presisi ganda/numerik/uang

Tampaknya tipe numerik adalah yang paling tidak bermasalah dalam hal kejutan perilaku. Tapi tidak peduli bagaimana keadaannya. Jadi mari kita mulai dengan mereka. Jadi…

Lupa cara menghitungnya

SELECT 0.1::real = 0.1

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

Apa masalahnya? Masalahnya adalah PostgreSQL mengubah konstanta yang tidak diketik 0.1 menjadi presisi ganda dan mencoba membandingkannya dengan 0.1 dari tipe sebenarnya. Dan ini adalah arti yang sangat berbeda! Idenya adalah untuk merepresentasikan bilangan real dalam memori mesin. Karena 0.1 tidak dapat direpresentasikan sebagai pecahan biner berhingga (dalam biner akan menjadi 0.0(0011)), angka-angka dengan kedalaman bit yang berbeda akan berbeda, sehingga menghasilkan angka-angka yang tidak sama. Secara umum, ini adalah topik untuk artikel terpisah, saya tidak akan menulis lebih detail di sini.

Kesalahannya dari mana?

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

Banyak orang tahu bahwa PostgreSQL mengizinkan notasi fungsional untuk casting tipe. Artinya, Anda tidak hanya dapat menulis 1::int, tetapi juga int(1), yang setara. Namun tidak untuk tipe yang namanya terdiri dari beberapa kata! Oleh karena itu, jika Anda ingin memasukkan nilai numerik ke tipe presisi ganda dalam bentuk fungsional, gunakan alias tipe float8 ini, yaitu SELECT float8(1).

Apa yang lebih besar dari ketidakterbatasan?

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

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

Lihat seperti apa rasanya! Ternyata ada sesuatu yang lebih besar dari tak terhingga, dan itu adalah NaN! Pada saat yang sama, dokumentasi PostgreSQL memandang kami dengan pandangan jujur dan mengklaim bahwa NaN jelas lebih besar daripada angka lainnya, dan, oleh karena itu, tak terhingga. Hal sebaliknya juga berlaku untuk -NaN. Halo, pecinta matematika! Namun kita harus ingat bahwa semua ini beroperasi dalam konteks bilangan real.

Pembulatan mata

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

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

Sambutan tak terduga lainnya dari pangkalan. Sekali lagi, ingat bahwa presisi ganda dan tipe numerik memiliki efek pembulatan yang berbeda. Untuk numerik - yang biasa, ketika 0,5 dibulatkan ke atas, dan untuk presisi ganda - 0,5 dibulatkan ke arah bilangan bulat genap terdekat.

Uang adalah sesuatu yang istimewa

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

Menurut PostgreSQL, uang bukanlah bilangan real. Menurut beberapa orang juga. Perlu kita ingat bahwa casting tipe uang hanya dapat dilakukan pada tipe numerik, sama seperti hanya tipe numerik yang dapat dilemparkan ke tipe uang. Tapi sekarang Anda bisa memainkannya sesuai keinginan hati Anda. Tapi itu bukan uang yang sama.

Pembuatan smallint dan urutan

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 tidak suka membuang waktu untuk hal-hal sepele. Apa urutan berdasarkan smallint? int, tidak kurang! Oleh karena itu, ketika mencoba menjalankan kueri di atas, database mencoba memasukkan smallint ke beberapa tipe bilangan bulat lainnya, dan melihat bahwa mungkin ada beberapa pemeran seperti itu. Pemeran mana yang harus dipilih? Dia tidak dapat memutuskan hal ini, dan karena itu mengalami error.

File nomor dua. "char"/char/varchar/teks

Sejumlah keanehan juga hadir pada tipe karakter. Mari kita mengenal mereka juga.

Trik macam apa ini?

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

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

Jenis "char" apa ini, badut macam apa ini? Kami tidak membutuhkannya... Karena berpura-pura menjadi karakter biasa, meskipun dalam tanda kutip. Dan ini berbeda dari char biasa, yang tanpa tanda kutip, karena ia hanya mengeluarkan byte pertama dari representasi string, sedangkan char normal mengeluarkan karakter pertama. Dalam kasus kami, karakter pertama adalah huruf P, yang dalam representasi unicode membutuhkan 2 byte, yang dibuktikan dengan mengonversi hasilnya ke tipe byte. Dan tipe “char” hanya mengambil byte pertama dari representasi unicode ini. Lalu mengapa tipe ini dibutuhkan? Dokumentasi PostgreSQL mengatakan bahwa ini adalah tipe khusus yang digunakan untuk kebutuhan khusus. Jadi kita tidak mungkin membutuhkannya. Tapi tatap matanya dan Anda tidak akan salah saat bertemu dengannya dengan perilaku istimewanya.

Spasi ekstra. Keluar dari akal pikiran

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

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

Lihatlah contoh yang diberikan. Semua hasilnya saya ubah secara khusus ke tipe bytea agar terlihat jelas apa yang ada disana. Di mana spasi tambahan setelah casting ke varchar(6)? Dokumentasi secara ringkas menyatakan: "Saat mentransmisikan nilai karakter ke tipe karakter lain, spasi tambahan akan dibuang." Ketidaksukaan ini harus diingat. Dan perhatikan bahwa jika konstanta string yang dikutip dilemparkan langsung ke tipe varchar(6), spasi di belakangnya akan dipertahankan. Begitulah keajaibannya.

File nomor tiga. json/jsonb

JSON adalah struktur terpisah yang menjalani kehidupannya sendiri. Oleh karena itu, entitasnya dan entitas PostgreSQL sedikit berbeda. Berikut ini contohnya.

Johnson dan Johnson. rasakan perbedaan nya

SELECT 'null'::jsonb IS NULL

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

Masalahnya adalah JSON memiliki entitas null sendiri, yang bukan analog dari NULL di PostgreSQL. Pada saat yang sama, objek JSON itu sendiri mungkin memiliki nilai NULL, sehingga ekspresi SELECT null::jsonb IS NULL (perhatikan tidak adanya tanda kutip tunggal) kali ini akan mengembalikan nilai true.

Satu huruf mengubah segalanya

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]}

Masalahnya adalah json dan jsonb adalah struktur yang sangat berbeda. Di json, objek disimpan apa adanya, dan di jsonb sudah disimpan dalam bentuk struktur yang diindeks dan diurai. Itulah sebabnya dalam kasus kedua, nilai objek dengan kunci 1 diganti dari [1, 2, 3] menjadi [7, 8, 9], yang masuk ke dalam struktur di bagian paling akhir dengan kunci yang sama.

Jangan minum air dari wajah Anda

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

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

PostgreSQL dalam implementasi JSONB-nya mengubah format bilangan real, menjadikannya bentuk klasik. Hal ini tidak terjadi pada tipe JSON. Agak aneh, tapi dia benar.

File nomor empat. tanggal/waktu/cap waktu

Ada juga beberapa keanehan dengan tipe tanggal/waktu. Mari kita lihat mereka. Izinkan saya segera membuat reservasi bahwa beberapa fitur perilaku menjadi jelas jika Anda memahami dengan baik esensi bekerja dengan zona waktu. Tapi ini juga merupakan topik untuk artikel terpisah.

Saya tidak mengerti Anda

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

Tampaknya, apa yang tidak bisa dipahami di sini? Namun database masih belum mengerti apa yang kita utamakan di sini—tahun atau hari? Dan dia memutuskan bahwa itu adalah tanggal 99 Januari 2008, yang mengejutkannya. Secara umum, saat mentransmisikan tanggal dalam format teks, Anda perlu hati-hati memeriksa seberapa benar database mengenalinya (khususnya, menganalisis parameter datestyle dengan perintah SHOW datestyle), karena ambiguitas dalam hal ini bisa sangat mahal.

Darimana asalmu?

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

Mengapa database tidak dapat memahami waktu yang ditentukan secara eksplisit? Karena zona waktu tidak mempunyai singkatan, melainkan nama lengkap, yang masuk akal hanya dalam konteks tanggal, karena memperhitungkan riwayat perubahan zona waktu, dan tidak berfungsi tanpa tanggal. Dan kata-kata di garis waktu itu sendiri menimbulkan pertanyaan - apa yang sebenarnya dimaksud oleh programmer? Oleh karena itu, semuanya logis di sini, jika Anda melihatnya.

Apa yang salah dengan dia?

Bayangkan situasinya. Anda memiliki bidang di tabel Anda dengan tipe timestamptz. Anda ingin mengindeksnya. Namun Anda memahami bahwa membuat indeks pada bidang ini tidak selalu dibenarkan karena selektivitasnya yang tinggi (hampir semua nilai jenis ini akan unik). Jadi, Anda memutuskan untuk mengurangi selektivitas indeks dengan memasukkan tipe ke tanggal. Dan Anda mendapat kejutan:

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

Apa masalahnya? Faktanya adalah bahwa untuk memasukkan tipe timestamptz ke tipe tanggal, nilai parameter sistem TimeZone digunakan, yang membuat fungsi konversi tipe bergantung pada parameter khusus, yaitu. tidak stabil. Fungsi seperti itu tidak diperbolehkan dalam indeks. Dalam hal ini, Anda harus secara eksplisit menunjukkan di zona waktu mana tipe cast dilakukan.

Padahal sekarang bukan sekarang sama sekali

Kami terbiasa now() mengembalikan tanggal/waktu saat ini, dengan mempertimbangkan zona waktu. Tapi lihat pertanyaan berikut:

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;

Tanggal/waktu dikembalikan sama tidak peduli berapa lama waktu telah berlalu sejak permintaan sebelumnya! Apa masalahnya? Faktanya adalah sekarang() bukanlah waktu saat ini, tetapi waktu mulai dari transaksi saat ini. Oleh karena itu, tidak berubah dalam transaksi. Setiap kueri yang diluncurkan di luar cakupan transaksi dibungkus dalam transaksi secara implisit, itulah sebabnya kami tidak memperhatikan bahwa waktu dikembalikan oleh perintah sederhana SELECT now(); sebenarnya, bukan yang sekarang... Jika Anda ingin mendapatkan waktu saat ini yang jujur, Anda perlu menggunakan fungsi clock_timestamp().

File nomor lima. sedikit

Sedikit aneh

SELECT '111'::bit(4)

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

Sisi mana yang harus ditambahkan bit jika terjadi ekstensi tipe? Tampaknya berada di sebelah kiri. Namun hanya pihak pangkalan yang mempunyai pendapat berbeda mengenai hal ini. Hati-hati: jika jumlah digit tidak cocok saat memasukkan suatu tipe, Anda tidak akan mendapatkan apa yang Anda inginkan. Hal ini berlaku untuk menambahkan bit ke kanan dan memotong bit. Juga di sebelah kanan...

File nomor enam. Array

Bahkan NULL tidak menyala

SELECT ARRAY[1, 2] || NULL

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

Sebagai orang normal yang mempelajari SQL, kami mengharapkan hasil dari ekspresi ini menjadi NULL. Tapi itu tidak ada di sana. Sebuah array dikembalikan. Mengapa? Karena dalam hal ini basis melemparkan NULL ke array integer dan secara implisit memanggil fungsi array_cat. Namun masih belum jelas mengapa “array cat” ini tidak mereset array. Perilaku ini juga hanya perlu diingat.

Meringkaskan. Ada banyak hal aneh. Kebanyakan dari mereka, tentu saja, tidak terlalu kritis untuk membicarakan perilaku yang terang-terangan tidak pantas. Dan lainnya dijelaskan oleh kemudahan penggunaan atau frekuensi penerapannya dalam situasi tertentu. Namun di saat yang sama, ada banyak kejutan. Oleh karena itu, Anda perlu mengetahuinya. Jika Anda menemukan hal lain yang aneh atau tidak biasa dalam perilaku apa pun, tulis di komentar, saya akan dengan senang hati menambahkan berkas yang tersedia di sana.

Sumber: www.habr.com

Tipe yang mencurigakan