Ύποπτοι τύποι

Δεν υπάρχει τίποτα ύποπτο για την εμφάνισή τους. Επιπλέον, σου φαίνονται γνωστά καλά και για πολύ καιρό. Αλλά αυτό μόνο μέχρι να τα ελέγξετε. Εδώ δείχνουν τον ύπουλο χαρακτήρα τους, λειτουργώντας τελείως διαφορετικά απ' ό,τι περίμενες. Και μερικές φορές κάνουν κάτι που σου σηκώνει τα μαλλιά - για παράδειγμα, χάνουν μυστικά δεδομένα που τους έχουν εμπιστευτεί. Όταν τους αντιμετωπίζεις, ισχυρίζονται ότι δεν γνωρίζονται, αν και στη σκιά δουλεύουν σκληρά κάτω από την ίδια κουκούλα. Ήρθε η ώρα να τα φέρουμε επιτέλους σε καθαρό νερό. Ας ασχοληθούμε και με αυτούς τους ύποπτους τύπους.

Η πληκτρολόγηση δεδομένων στην PostgreSQL, παρ' όλη τη λογική της, μερικές φορές παρουσιάζει πολύ περίεργες εκπλήξεις. Σε αυτό το άρθρο θα προσπαθήσουμε να ξεκαθαρίσουμε μερικές από τις ιδιορρυθμίες τους, να κατανοήσουμε τον λόγο της περίεργης συμπεριφοράς τους και να καταλάβουμε πώς να μην αντιμετωπίζετε προβλήματα στην καθημερινή πρακτική. Για να πω την αλήθεια, συνέταξα αυτό το άρθρο και ως ένα είδος βιβλίου αναφοράς για τον εαυτό μου, ένα βιβλίο αναφοράς που θα μπορούσε εύκολα να αναφερθεί σε αμφιλεγόμενες περιπτώσεις. Ως εκ τούτου, θα αναπληρωθεί καθώς ανακαλύπτονται νέες εκπλήξεις από ύποπτους τύπους. Λοιπόν, πάμε, ω ακούραστοι ιχνηλάτες βάσεων δεδομένων!

Φάκελος νούμερο ένα. πραγματική/διπλή ακρίβεια/αριθμητικό/χρήματα

Φαίνεται ότι οι αριθμητικοί τύποι είναι οι λιγότερο προβληματικοί όσον αφορά τις εκπλήξεις στη συμπεριφορά. Αλλά όπως και να είναι. Ας ξεκινήσουμε λοιπόν με αυτούς. Ετσι…

Ξεχάσατε πώς να μετράτε

SELECT 0.1::real = 0.1

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

Τι συμβαίνει? Το πρόβλημα είναι ότι η PostgreSQL μετατρέπει την άτυπη σταθερά 0.1 σε διπλή ακρίβεια και προσπαθεί να τη συγκρίνει με 0.1 πραγματικού τύπου. Και αυτές είναι τελείως διαφορετικές έννοιες! Η ιδέα είναι να αναπαραστήσουμε πραγματικούς αριθμούς στη μνήμη της μηχανής. Εφόσον το 0.1 δεν μπορεί να αναπαρασταθεί ως ένα πεπερασμένο δυαδικό κλάσμα (θα ήταν 0.0(0011) σε δυαδικό σύστημα), οι αριθμοί με διαφορετικά βάθη bit θα είναι διαφορετικοί, επομένως θα προκύπτει ότι δεν είναι ίσοι. Σε γενικές γραμμές, αυτό είναι ένα θέμα για ένα ξεχωριστό άρθρο· δεν θα γράψω λεπτομερέστερα εδώ.

Από πού προέρχεται το σφάλμα;

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 σε κάποιον άλλο τύπο ακέραιου αριθμού και βλέπει ότι μπορεί να υπάρχουν αρκετές τέτοιες εκδόσεις. Ποιο καστ να διαλέξω; Δεν μπορεί να το αποφασίσει αυτό, και ως εκ τούτου κολλάει με ένα σφάλμα.

Αρχείο νούμερο δύο. "char"/char/varchar/κείμενο

Μια σειρά από παραξενιές υπάρχουν επίσης στους τύπους χαρακτήρων. Ας τους γνωρίσουμε κι εμείς.

Τι είδους κόλπα είναι αυτά;

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

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

Τι τύπος "χαρακτήρα" είναι αυτός, τι είδους κλόουν είναι αυτός; Δεν τα χρειαζόμαστε... Επειδή προσποιείται ότι είναι ένας συνηθισμένος χαρακτήρας, παρόλο που είναι σε εισαγωγικά. Και διαφέρει από έναν κανονικό χαρακτήρα, ο οποίος είναι χωρίς εισαγωγικά, στο ότι εξάγει μόνο το πρώτο byte της αναπαράστασης συμβολοσειράς, ενώ ένας κανονικός χαρακτήρας εξάγει τον πρώτο χαρακτήρα. Στην περίπτωσή μας, ο πρώτος χαρακτήρας είναι το γράμμα P, το οποίο στην αναπαράσταση unicode καταλαμβάνει 2 byte, όπως αποδεικνύεται από τη μετατροπή του αποτελέσματος στον τύπο bytea. Και ο τύπος "char" παίρνει μόνο το πρώτο byte αυτής της αναπαράστασης 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, ώστε να φαίνεται καθαρά τι υπήρχε. Πού βρίσκονται οι χώροι μετά το casting στο 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 (σημειώστε την απουσία μεμονωμένων εισαγωγικών) θα επιστρέψει 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, κάτι που της ταράζει το μυαλό. Γενικά, κατά τη μετάδοση ημερομηνιών σε μορφή κειμένου, πρέπει να ελέγχετε πολύ προσεκτικά πόσο σωστά τις αναγνώρισε η βάση δεδομένων (ιδίως, αναλύστε την παράμετρο 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

Τι συμβαίνει? Το γεγονός είναι ότι για τη μετάδοση ενός τύπου 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

Ποια πλευρά πρέπει να προστεθούν τα bit σε περίπτωση επέκτασης τύπου; Φαίνεται να είναι στα αριστερά. Αλλά μόνο η βάση έχει διαφορετική άποψη για αυτό το θέμα. Προσέξτε: εάν ο αριθμός των ψηφίων δεν ταιριάζει κατά τη μετάδοση ενός τύπου, δεν θα πάρετε αυτό που θέλατε. Αυτό ισχύει τόσο για την προσθήκη bits στα δεξιά όσο και για την περικοπή bit. Επίσης στα δεξιά...

Φάκελος αριθμός έξι. Πίνακες

Ακόμα και το NULL δεν πυροβόλησε

SELECT ARRAY[1, 2] || NULL

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

Καθώς τα κανονικά άτομα μεγάλωσαν στην SQL, αναμένουμε ότι το αποτέλεσμα αυτής της έκφρασης θα είναι NULL. Αλλά δεν ήταν εκεί. Επιστρέφεται ένας πίνακας. Γιατί; Διότι σε αυτήν την περίπτωση η βάση εκπέμπει NULL σε έναν ακέραιο πίνακα και καλεί σιωπηρά τη συνάρτηση array_cat. Ωστόσο, εξακολουθεί να παραμένει ασαφές γιατί αυτή η "γάτα συστοιχίας" δεν επαναφέρει τη συστοιχία. Αυτή η συμπεριφορά πρέπει επίσης να θυμόμαστε.

Συνοψίζω. Υπάρχουν πολλά περίεργα πράγματα. Οι περισσότεροι βέβαια δεν είναι τόσο επικριτικοί ώστε να μιλάνε για κατάφωρα ανάρμοστη συμπεριφορά. Και άλλα εξηγούνται από την ευκολία χρήσης ή τη συχνότητα εφαρμογής τους σε ορισμένες περιπτώσεις. Ταυτόχρονα όμως υπάρχουν πολλές εκπλήξεις. Επομένως, πρέπει να γνωρίζετε γι 'αυτούς. Εάν βρείτε οτιδήποτε άλλο περίεργο ή ασυνήθιστο στη συμπεριφορά οποιουδήποτε τύπου, γράψτε στα σχόλια, θα χαρώ να προσθέσω στους φακέλους που είναι διαθέσιμοι σε αυτά.

Πηγή: www.habr.com