Tipuri stricte: ce nu mai merge ca în SQLite
SQLite îți primește aproape orice valoare în orice coloană. PostgreSQL nu: fiecare coloană are un tip, iar serverul refuză valorile care nu se potrivesc. Pare mai sever, dar așa nu ajung niciodată în baza de date prețuri scrise „zece lei” sau id-uri cu litere. În această lecție vezi ce erori apar, cum se împart numerele și cum lipești texte.
PostgreSQL verifică fiecare valoare
Lucrăm pe tabelul carti din lecția trecută, la care am adăugat o coloană stoc integer. Ca să vedem mai multe erori în același script, prima linie este \set ON_ERROR_STOP off: ea îi spune lui psql să continue după o eroare, în loc să se oprească.
\set ON_ERROR_STOP off
INSERT INTO carti (id, titlu, pret) VALUES ('abc', 'Test', 10);
INSERT INTO carti (id, titlu, pret) VALUES (5, 'Test', 'zece lei');
SELECT count(*) AS carti FROM carti;ERROR: invalid input syntax for type integer: "abc"
LINE 1: INSERT INTO carti (id, titlu, pret) VALUES ('abc', 'Test', 10);
^
ERROR: invalid input syntax for type numeric: "zece lei"
LINE 1: INSERT INTO carti (id, titlu, pret) VALUES (5, 'Test', 'zece lei');
^
carti
-------
4
(1 rând)
Aceleași două INSERT-uri ar fi mers în SQLite: ar fi salvat textul abc ca id și zece lei ca preț. Bug-ul ar fi apărut abia mai târziu, la un calcul sau într-un raport. PostgreSQL îl oprește la intrare.
text, varchar(n) și char(n)
Pentru texte, PostgreSQL are trei tipuri. text și varchar(n) au aceeași performanță; varchar(n) doar limitează lungimea. char(n) completează textul cu spații până la n caractere și este bine să îl eviți.
\set ON_ERROR_STOP off
CREATE TABLE coduri (scurt varchar(5), fix char(5));
INSERT INTO coduri VALUES ('ABCDEFG', 'x');
INSERT INTO coduri VALUES ('AB', 'AB');
SELECT scurt, fix, octet_length(fix) AS octeti_fix FROM coduri;CREATE TABLE ERROR: value too long for type character varying(5) INSERT 0 1 scurt | fix | octeti_fix -------+-------+------------ AB | AB | 5 (1 rând)
- value too long for type character varying(5) (cod 22001): textul ABCDEFG are 7 caractere, iar coloana acceptă cel mult 5.
- fix pare AB, dar în realitate ocupă 5 octeți: AB plus trei spații. Aceste spații ascunse produc comparații ciudate.
- Recomandarea din documentația PostgreSQL: folosește text, iar când chiar ai o limită de business (un cod poștal, un cod de țară) folosește varchar(n).
Împărțirea: întreg sau zecimal
Când împarți două numere întregi, rezultatul este tot întreg: partea zecimală se pierde. Dacă unul dintre numere este zecimal, rezultatul este zecimal:
SELECT 7 / 2 AS intreg, 7 / 2.0 AS zecimal, 7 % 2 AS rest;
intreg | zecimal | rest
--------+--------------------+------
3 | 3.5000000000000000 | 1
(1 rând)
Același lucru se întâmplă cu coloanele: sum(stoc) / count(*) împarte doi întregi, deci dă un întreg, pe când avg(stoc) întoarce un numeric cu multe zecimale. Pentru o valoare frumoasă folosești round(x, 2):
SELECT sum(stoc) / count(*) AS impartire_intreaga,
avg(stoc) AS medie,
round(avg(stoc), 2) AS medie_rotunjita
FROM carti; impartire_intreaga | medie | medie_rotunjita
--------------------+--------------------+-----------------
5 | 5.7500000000000000 | 5.75
(1 rând)
vandute / total * 100 cu două coloane integer dă aproape mereu 0 (de exemplu 3 / 4 = 0). Scrie vandute * 100.0 / total sau convertește una dintre valori la numeric.
Lipirea textelor cu ||
Operatorul || lipește (concatenează) texte. Un număr lipit de un text este transformat automat în text:
SELECT titlu || ' de ' || autor AS carte, 'Stoc: ' || stoc AS mesaj FROM carti ORDER BY id;
carte | mesaj ------------------------------------+---------- Ion de Liviu Rebreanu | Stoc: 12 Maitreyi de Mircea Eliade | Stoc: 0 Enigma Otiliei de George Călinescu | Stoc: 7 Baltagul de Mihail Sadoveanu | Stoc: 4 (4 rânduri)
Dacă una dintre valori este NULL, tot rezultatul devine NULL:
SELECT 'Autor: ' || NULL AS rezultat;
rezultat ---------- NULL (1 rând)
Comparații între tipuri
Textele se compară caracter cu caracter, numerele după valoare. De aceea '10' < '9' este adevărat ca text (caracterul 1 vine înaintea lui 9), dar fals ca număr:
SELECT '10' < '9' AS ca_text, 10 < 9 AS ca_numar;
ca_text | ca_numar ---------+---------- t | f (1 rând)
Iar o coloană integer nu se poate compara direct cu o coloană text. SELECT id = titlu FROM carti; dă eroarea operator does not exist: integer = text, cu sugestia (HINT) să adaugi o conversie explicită. Conversiile sunt subiectul lecției următoare.
Rezumat
O valoare care nu se potrivește cu tipul coloanei dă eroarea 22P02, iar rândul nu se salvează.
text pentru aproape orice; varchar(n) doar pentru o limită reală (22001 dacă e depășită); evită char(n).
7 / 2 = 3, 7 / 2.0 = 3.5000000000000000; rotunjește cu round(x, 2).
|| lipește texte; un NULL face tot rezultatul NULL.
Fă un mic raport pe tabelul carti (4 cărți, cu coloanele id, titlu, autor, pret, in_stoc, stoc).
- Într-un prim SELECT, afișează prețul mediu rotunjit la 2 zecimale (alias pret_mediu) și media întreagă a stocului calculată ca sum(stoc) / count(*) (alias stoc_mediu).
- Într-un al doilea SELECT, afișează titlul și autorul lipite cu ' - ' între ele (alias carte), ordonate după id.
Soluția exercițiului
SELECT round(avg(pret), 2) AS pret_mediu, sum(stoc) / count(*) AS stoc_mediu FROM carti; SELECT titlu || ' - ' || autor AS carte FROM carti ORDER BY id;
