Ja saps què és SQL i com s'escriu. Toca ara entendre sobre què actua: l'estructura on viuen les dades. En aquesta lliçó desmuntarem la jerarquia completa —servidor, base de dades, esquema, taula, fila, columna—, veurem per què l'analogia amb un full de càlcul ajuda al principi però es trenca de seguida, i recorrerem en detall el catàleg de tipus de dades de PostgreSQL. Aquest últim punt és més important del que sembla: triar malament un tipus és un error que es paga durant anys, i el cas dels diners desats en coma flotant és l'exemple canònic de per què. Acabarem aprenent a inspeccionar taules que ja existeixen, una habilitat que necessitaràs cada vegada que t'enfrontis a una base de dades aliena.

1. La jerarquia: servidor, base de dades, esquema, taula
2. Taules, files i columnes
3. L'analogia del full de càlcul (i on es trenca)
4. Tipus de dades numèrics
5. Per què els diners no van mai en coma flotant
6. Tipus de text
7. Tipus de data i hora
8. Booleans, UUID i JSONB
9. NULL: l'absència de valor
10. Equivalències entre PostgreSQL, MySQL i SQLite
11. Com inspeccionar taules existents
12. Errors habituals i consells
13. Exercicis
14. Conclusió

A PostgreSQL els objectes s'organitzen en quatre nivells:

Dues conseqüències pràctiques d'aquesta jerarquia:

1. No pots consultar dues bases de dades alhora. Si botigaverda i proves_sql viuen al mateix servidor, una consulta no les pot combinar directament (caldrien extensions com dblink o postgres_fdw). A MySQL, en canvi, sí que és habitual escriure SELECT ... FROM altra_base.taula, perquè allà "base de dades" i "esquema" són gairebé sinònims.
2. Els esquemes sí que es combinen lliurement. Dins de botigaverda podries tenir un esquema vendes i un altre analitica i consultar-los junts sense cap problema.

Tota base de dades PostgreSQL neix amb un esquema anomenat public. Si crees una taula sense indicar esquema, va a parar allà, i si la consultes sense prefix es busca allà. Aquestes dues sentències són equivalents a la nostra configuració:

L'ordre de cerca el determina el paràmetre search_path:

Significa: "busca primer un esquema que es digui com l'usuari connectat; si no existeix, busca a public". Els esquemes serveixen per organitzar bases grans (separar per àrea funcional, per client, per entorn) i per evitar col·lisions de noms. Tota BotigaVerda viu a public, així que no ens en tornarem a preocupar.

Una taula és una col·lecció de files que comparteixen la mateixa estructura. Cada taula es defineix per:

• Un nom (productes).
• Un conjunt ordenat de columnes, cadascuna amb nom i tipus de dada.
• Opcionalment, restriccions que limiten quins valors són vàlids (mòdul 5).

Una vista simplificada de productes:

Terminologia formal davant de la col·loquial:

Tres propietats fonamentals que convé gravar des del principi:

1. Totes les files tenen les mateixes columnes. No hi ha cap fila "amb un camp extra". Si una dada no s'aplica, s'hi desa NULL.
2. Cada columna té un únic tipus. No pots desar 'demà' en una columna DATE.
3. Les files no tenen ordre intrínsec. Una taula és un conjunt. Si no demanes ORDER BY explícitament, el motor te les pot retornar en qualsevol ordre, i aquest ordre pot canviar demà. És un error clàssic refiar-se de l'"ordre natural".

Al principi ajuda pensar en una taula com un full d'Excel: la primera fila són les capçaleres (columnes) i les següents, les dades (files). L'analogia funciona... fins a cert punt.

La diferència conceptual més profunda és la tipificació estricta. A Excel, una columna de preus pot tenir 12,50, 12.50, "12,50 €" i dotze amb cinquanta, i ningú no t'avisa fins que la suma surt malament. A PostgreSQL, si la columna és NUMERIC(10,2), el motor rebutja qualsevol cosa que no sigui un número:

Aquest error, que pot semblar una molèstia, és exactament el valor que aporta una base de dades: les dades incorrectes no arriben a entrar.

Sobre NUMERIC(p,s):

• p (precisió) és el nombre total de dígits.
• s (escala) és quants d'aquests dígits van després de la coma.
• NUMERIC(10,2) admet fins a 99 999 999,99 → vuit dígits enters i dos decimals.

A BotigaVerda fem servir NUMERIC(10,2) per a preu, cost, despeses_enviament, preu_unitari, import i salari, i NUMERIC(4,2) per a descompte (una fracció entre 0 i 1, amb dos decimals).

Nota sobre SERIAL: no és un tipus real, sinó una drecera que crea un INTEGER més una seqüència que l'autoincrementa. Des de PostgreSQL 10 la forma recomanada per l'estàndard és GENERATED BY DEFAULT AS IDENTITY, que és la que fa servir l'script del curs. Ho veuràs amb detall al mòdul 5.

REAL i DOUBLE PRECISION emmagatzemen els números en coma flotant binària (estàndard IEEE 754). El problema és que molts decimals que en base 10 són exactes, en base 2 són periòdics: 0,1 en binari és infinit, igual que 1/3 ho és en decimal. L'ordinador en desa una aproximació, i aquests errors minúsculs s'acumulen.

Comprova-ho:

I el cas que t'arruïnaria un tancament comptable:

Portat a BotigaVerda: si desàvem preu com a REAL i sumàvem les 47 línies de comanda de la base de dades, el total podria sortir 143,20999999998 en lloc de 143,21. Multiplica-ho per milers de comandes al mes i tindràs un descompensament comptable impossible de justificar.

Regla sense excepcions: diners → NUMERIC. Mai FLOAT, REAL ni DOUBLE PRECISION. La penalització de velocitat és irrellevant comparada amb un cèntim perdut.

Existeix a més el tipus MONEY a PostgreSQL, però no es recomana: depèn de la configuració regional del servidor i no admet bé diverses divises. Fes servir NUMERIC.

Una particularitat de PostgreSQL que sorprèn qui ve d'altres motors: TEXT i VARCHAR tenen exactament el mateix rendiment. Internament són el mateix tipus; VARCHAR(n) només hi afegeix una comprovació de llargada. No hi ha cap avantatge de velocitat a posar-hi un límit.

Aleshores, quan posar VARCHAR(n)? Quan el límit signifiqui alguna cosa:

• pais VARCHAR(60): nom de país, un límit raonable.
• email VARCHAR(120): hi ha un màxim pràctic conegut.
• descripcio TEXT: no sabem quant hi escriurà ningú.
• comentari TEXT: una ressenya pot ser llarga.

Evita CHAR(n). Omple amb espais a la dreta i provoca comparacions sorprenents:

El valor es desa com a 'ES ' però en comparar-lo s'ignoren els espais finals, cosa que genera confusió constant en exportar o concatenar.

Nota de dialecte: a MySQL sí que hi ha diferències de rendiment i emmagatzematge entre CHAR, VARCHAR i TEXT (els TEXT es desen fora de la fila i no admeten valor per defecte). El que aquí és indiferent, allà no ho és.

La distinció entre TIMESTAMP i TIMESTAMPTZ és la que més problemes causa en producció:

• TIMESTAMP desa literalment el que li dones. Si un client francès i un altre de català registren "18:30", es desen igual encara que siguin moments diferents.
• TIMESTAMPTZ converteix a UTC en desar i a la zona del client en llegir. Representa un instant real del temps.

Regla pràctica: si el moment té rellevància real (quan va passar alguna cosa), fes servir TIMESTAMPTZ. Si és una data de calendari sense hora (data d'una comanda, data de naixement), fes servir DATE. BotigaVerda fa servir DATE en totes les seves dates perquè són dates de calendari, no instants.

Dialecte: MySQL té DATETIME (sense zona) i TIMESTAMP (amb conversió a UTC, però limitat fins al 2038). SQLite no té tipus de data: desa text ISO, números o julian days, i les funcions de data operen sobre aquestes representacions.

Desa TRUE, FALSE o NULL. Ocupa 1 byte. A BotigaVerda el fan servir productes.actiu i proveidors.actiu.

Aquest NULL té sentit de negoci: "actiu = TRUE" és un producte a la venda, "FALSE" un de descatalogat, i NULL significaria "encara no ho hem decidit". Per això els booleans en SQL tenen tres estats, no dos.

Un identificador universal de 128 bits, de l'estil a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. Ocupa 16 bytes.

BotigaVerda fa servir INTEGER per als seus id perquè és una base petita, didàctica i on poder escriure WHERE id = 7 és un avantatge d'aprenentatge.

PostgreSQL permet desar documents JSON en una columna. JSON desa el text tal qual; JSONB el desa en format binari indexat, i és el que es fa servir a la pràctica. Serveix per a dades semiestructurades amb un esquema variable (atributs específics de cada producte, respostes d'una passarel·la de pagament, configuracions).

Ho esmentem aquí perquè sàpigues que existeix; el seu ús complet es tracta a la lliçó 10-06. I un advertiment: JSONB no és una excusa per no dissenyar bé les teves taules. El que és estructurat va en columnes.

NULL no és zero, ni cadena buida, ni FALSE. Significa "aquí no hi ha valor" o "no se sap".

A BotigaVerda hi ha tres nuls amb significat de negoci ben definit:

L'essencial ara és entendre que NULL es propaga per qualsevol operació:

Qualsevol càlcul que toqui un NULL retorna NULL, perquè operar amb una cosa desconeguda produeix una cosa desconeguda. I com ja vas veure a la lliçó anterior, per comprovar si una cosa és nul·la es fa servir IS NULL, mai = NULL.

Distingeix aquests tres casos, que no són el mateix:

El tractament complet dels nuls —com afecten les agregacions, els JOIN i els filtres— és la lliçó 04-03. De moment n'hi ha prou amb el concepte.

Si has de portar un esquema o llegir codi aliè, aquesta taula t'estalviarà temps:

La diferència més perillosa és a l'última fila. SQLite fa servir afinitat de tipus: si declares una columna INTEGER i hi insereixes el text 'hola', el desa sense protestar. És còmode per prototipar i desastrós per garantir integritat. És la raó principal per la qual aquest curs fa servir PostgreSQL.

Quan arribes a un projecte nou, el primer és entendre'n l'esquema. Hi ha dos camins.

Una sola ordre et dona columnes, tipus, nul·labilitat, valors per defecte, índexs, claus foranes i quines altres taules apunten a aquesta. Fes servir \d+ productes per veure a més la mida i els comentaris.

L'estàndard SQL defineix un esquema de metadades anomenat information_schema, disponible a PostgreSQL, MySQL i SQL Server. El seu avantatge sobre \d és que és SQL normal: el pots filtrar, ordenar i fer servir des de qualsevol llenguatge.

Vistes útils d'information_schema:

I una consulta que faràs servir sovint per fer-te un mapa ràpid d'una base desconeguda:

information_schema és l'estàndard, però PostgreSQL té a més el seu catàleg propi pg_catalog (pg_tables, pg_class, pg_attribute), més complet i més ràpid, encara que no portable. Les metaordres \d consulten precisament aquest catàleg.

• Desar diners en FLOAT o REAL. L'error més car d'aquesta lliçó. Sempre NUMERIC(10,2).
• Desar dates com a text. VARCHAR per a una data impedeix ordenar bé, calcular diferències i validar. Fes servir DATE o TIMESTAMPTZ.
• Desar números que no són quantitats com a números. Un codi postal (03001), un telèfon o un NIF són text: si els deses com a INTEGER perdràs el zero inicial i el +34.
• Posar VARCHAR(255) per costum. El 255 ve de MySQL antic. A PostgreSQL fes servir TEXT tret que hi hagi un límit de negoci real.
• Fer servir CHAR(n). L'ompliment amb espais provoca errors subtils. Pràcticament mai no és l'elecció correcta.
• Confondre NULL amb 0 o ''. Són tres coses diferents i es comporten de manera diferent en filtres i agregacions.
• Refiar-te de l'ordre de les files. Sense ORDER BY no hi ha ordre garantit, per molt que avui surtin ordenades.
• Consell: tria el tipus pensant a cinc anys vista. Canviar el tipus d'una columna amb milions de files en producció és una operació delicada (mòdul 5).
• Consell: \d taula és la teva primera ordre en qualsevol base aliena. Abans d'escriure una consulta, mira'n l'estructura.
• Consell: posa noms de taula en plural i de columna en singular. productes.nom es llegeix millor que producte.noms.

Per a cada dada de BotigaVerda, tria el tipus PostgreSQL més adequat i justifica'l en una línia:

1. El preu de venda d'un producte.
2. El codi de país d'un proveïdor en format ISO (ES, PT, FR).
3. El comentari d'una ressenya.
4. La puntuació d'una ressenya (1 a 5).
5. La data en què es va registrar un client.
6. Si un producte està actiu o no.
7. L'instant exacte en què es va confirmar un pagament, amb clients en tres països.
8. El descompte aplicat a una línia de comanda (fracció de 0 a 1, dos decimals).

Executa aquestes expressions i explica què demostra cadascuna:

Escriu una consulta sobre information_schema que mostri totes les columnes de tipus numeric de la base de dades botigaverda, indicant en quina taula són i amb quina precisió i escala. Ordena-les per taula i per posició dins de la taula.

Solució 1

Solució 2

Solució 3

Resultat esperat sobre BotigaVerda:

Observa que data_type retorna 'numeric' en minúscules, i que la precisió i l'escala vénen en columnes separades: information_schema normalitza els tipus al vocabulari de l'estàndard SQL, no al nom que vas escriure en crear la taula.

En aquesta lliçó has recorregut l'estructura sobre la qual actua SQL:

• La jerarquia servidor → base de dades → esquema (public) → taula → columnes i files, i per què no es poden consultar dues bases de dades alhora a PostgreSQL.
• Una taula és un conjunt de files amb la mateixa estructura, sense ordre intrínsec, amb un tipus per columna verificat pel motor: aquí hi ha la gran diferència amb un full de càlcul.
• El catàleg de tipus de PostgreSQL: numèrics (INTEGER, BIGINT, NUMERIC(p,s), REAL), text (TEXT, VARCHAR(n), CHAR(n)), data i hora (DATE, TIMESTAMP, TIMESTAMPTZ, INTERVAL), BOOLEAN, UUID i JSONB.
• La regla innegociable: els diners van en NUMERIC, mai en coma flotant, perquè 0.1 + 0.2 <> 0.3 en binari.
• NULL com a absència de valor, diferent de 0 i de '', que es propaga en tota operació.
• Les equivalències de tipus entre PostgreSQL, MySQL i SQLite, i el perill del sistema de tipus dinàmic de SQLite.
• Com inspeccionar una base aliena amb \dt, \d taula i information_schema.columns.

A la lliçó següent, El model relacional: claus primàries i foranes, veurem com les taules deixen d'estar aïllades i es relacionen entre si: què és una clau primària i per què fem servir id subrogats, com les claus foranes garanteixen que no hi hagi comandes de clients inexistents, què passa en esborrar un registre del qual depenen d'altres, com es representen les cardinalitats 1:1, 1:N i N:M —amb linies_comanda com a exemple real de taula pont— i com la normalització explica per què l'esquema de BotigaVerda està partit en nou taules i no en una de sola.