...
← Curs PostgreSQL: toate lecțiile
Lecția 5 · Introducere

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)
1
invalid input syntax for type integer: "abc": textul abc nu poate deveni număr întreg. Codul acestei erori este 22P02.
2
LINE 1 și semnul ^ îți arată exact valoarea problematică din instrucțiune.
3
Ambele INSERT-uri au fost respinse: tabelul are tot 4 cărți.
În SQLite

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)
Atenție la procente

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

1Tipuri stricte

O valoare care nu se potrivește cu tipul coloanei dă eroarea 22P02, iar rândul nu se salvează.

2Texte

text pentru aproape orice; varchar(n) doar pentru o limită reală (22001 dacă e depășită); evită char(n).

3Împărțirea

7 / 2 = 3, 7 / 2.0 = 3.5000000000000000; rotunjește cu round(x, 2).

4Concatenarea

|| lipește texte; un NULL face tot rezultatul NULL.

Exercițiul tău

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.
round(avg(pret), 2); pentru al doilea rezultat: titlu || ' - ' || autor.

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;
Excelent! Continuă cu lecția următoare →
script.sqlPostgreSQL
ConsolăSe încarcă...
Mediul de lucru se încarcă. Poți citi lecția între timp.
Rulează codul cu butonul de sus sau cu Ctrl + Enter / Cmd + Enter.
Parcursul tău Primii pași cu PostgreSQL Lecția 5 · Tipuri stricte: ce nu mai merge ca în SQLite
Progres 0% 0 din 12
Lecția curentă 5 / 10 Introducere