El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

La lliçó anterior va tancar el mòdul 10 amb una frase: "el que falta no són més funcions, sinó context". Aquest mòdul és aquest context, i comença pel més concret que hi ha: els problemes que et demanaran resoldre. Perquè la caixa d'eines ja és plena —JOIN, agregats, subconsultes, CTE, funcions de finestra, JSON— però en un projecte real ningú no et demana "un LEFT JOIN": et demanen "un llistat de productes amb filtres que l'usuari tria", "el quadre de comandament de direcció", "els clients que ja no compren" o "exporta això per al gestor".

La bona notícia és que aquests encàrrecs es repeteixen. Canvia el negoci, canvien els noms de les taules, i el patró és el mateix. Aquesta lliçó és el catàleg d'aquests patrons: onze casos d'ús, cadascun amb el seu plantejament de negoci, la seva solució sobre BotigaVerda i una nota de quina eina del curs el resol. No hi ha sintaxi nova. Hi ha reconeixement de patrons, que és una cosa diferent i molt més útil.

Contingut

Llistat paginat amb filtres opcionals
Cerca: prefix, contingut i full-text
Informe de KPI en una sola fila
Top N i rànquing
Detecció de buits: l'anti-join
Sèries temporals sense buits
Cohorts de clients
Detecció i neteja de duplicats
Auditoria: qui va canviar què i quan
Exportació a un altre sistema
Càrrega des de fitxer amb taula de staging
Taula resum: cas d'ús → eina → lliçó
Errors habituals i consells
Exercicis
Conclusió

Llistat paginat amb filtres opcionals

L'encàrrec. "La pantalla de catàleg té quatre filtres —categoria, preu màxim, text i només amb estoc—, i l'usuari pot omplir els que vulgui. A més té paginació." És el cas d'ús número u del món, i el que més vegades s'escriu malament. El problema no és el WHERE: és que no saps quin serà el WHERE fins que l'usuari premi "cerca". Hi ha dos enfocaments, i convé conèixer-los tots dos.

El patró `(:param IS NULL OR columna = :param)`

Una sola consulta, amb un paràmetre per filtre que es neutralitza quan arriba nul:

SELECT p.id, p.nom, cat.nom AS categoria, p.preu, p.stock
FROM   productes  AS p
JOIN   categories AS cat ON cat.id = p.categoria_id
WHERE  p.actiu
  AND  (:categoria_id IS NULL OR p.categoria_id = :categoria_id)
  AND  (:preu_max     IS NULL OR p.preu        <= :preu_max)
  AND  (:text         IS NULL OR p.nom ILIKE '%' || :text || '%')
  AND  (NOT :nomes_stock OR p.stock > 0)
ORDER  BY p.preu DESC, p.id
LIMIT  5;

Amb els quatre paràmetres a NULL/false (primera pàgina del catàleg, sense filtrar):

id	nom	categoria	preu	stock
15	Te verd matcha cerimonial 30 g	Begudes	22.00	40
6	Crema facial d'àloe vera 50 ml	Cosmètica natural	18.90	60
8	Oli corporal d'ametlles 200 ml	Cosmètica natural	14.25	45
13	Espelmes de cera de soja (pack 2)	Llar sostenible	13.75	0
1	Oli d'oliva verge extra 500 ml	Alimentació	12.50	120

(5 primeres de 19 files: els productes actius; el 20, descatalogat, no hi apareix.) Amb :categoria_id = 2 i :preu_max = 15.00 la mateixa consulta retorna 3 files —l'oli corporal (14,25 €), el xampú sòlid (8,40 €) i el bàlsam labial (4,60 €)—, i amb :nomes_stock = true desapareixerien les espelmes de cera, les úniques amb estoc 0. Els seus avantatges són reals: una sola consulta per mantenir, cap risc d'injecció (11-03) i un pla cachejable. I el seu cost també: (:p IS NULL OR col = :p) no és sargable (08-04) —el motor no sap per endavant si la columna estarà filtrada—, així que el pla ha de servir per als setze casos possibles i no és òptim per a cap. Amb 19 productes tant és; amb 19 milions i un filtre molt selectiu, és un Seq Scan on hi havia un Index Scan.

Mitigació a PostgreSQL: una sentència preparada passa a pla genèric a partir de la sisena execució. Amb SET plan_cache_mode = force_custom_plan el motor replanifica amb els valors concrets i descarta les branques neutralitzades. És la sortida quan el patró funciona bé tret que sigui en un filtre concret.

L'alternativa: construir el SQL a l'aplicació

L'altre enfocament és compondre el WHERE en codi, afegint-hi només les condicions que l'usuari ha omplert:

sql, params = ["SELECT p.id, p.nom, p.preu FROM productes AS p WHERE p.actiu"], {}
if categoria_id is not None:
    sql.append("AND p.categoria_id = %(categoria_id)s"); params["categoria_id"] = categoria_id
if preu_max is not None:
    sql.append("AND p.preu <= %(preu_max)s");            params["preu_max"] = preu_max
cur.execute(" ".join(sql), params)          # ✅ els VALORS continuen sent paràmetres

La línia vermella és a l'últim renglot: es construeix el text de les condicions, mai els valors, que continuen viatjant com a paràmetres. Concatenar f"AND p.preu <= {preu_max}" és exactament la vulnerabilitat d'11-03, i no deixa de ser-ho perquè la dada "sembli" un número.

	Patró `:param IS NULL OR …`	SQL construït a l'aplicació
Consultes per mantenir	Una	Una plantilla + la lògica de composició
Qualitat del pla	Genèrica: mediocre per a tots	Òptima per a cada combinació
Risc d'injecció	Cap	Baix si només compons condicions; alt si compons valors
Llegibilitat / depuració	Alta / registra sempre el mateix SQL	Menor / cal registrar el SQL final
Quan triar-lo	Pocs filtres, taules mitjanes, equips que volen SQL fix	Molts filtres, taules grans, rendiment crític

El criteri del curs: comença pel patró d'una sola consulta; passa't a la composició només quan mesuris (08-05) que el pla genèric t'està costant. I per a la paginació, el de 08-04: LIMIT/OFFSET per a paginadors numerats petits, keyset per a scroll infinit i API.

Cerca: prefix, contingut i full-text

L'encàrrec. "Que el cercador de la botiga trobi el producte encara que el client escrigui mitja paraula." Hi ha tres nivells, en ordre de cost creixent, i triar el més barat que resolgui el problema és la decisió:

-- Nivell 1: per PREFIX. Fa servir un B-tree normal si la col·lació és l'adequada.
SELECT id, nom FROM productes WHERE nom ILIKE 'oli%' ORDER BY id;
-- Nivell 2: per CONTINGUT. No fa servir B-tree: necessita pg_trgm + GIN (08-03).
SELECT id, nom FROM productes WHERE nom ILIKE '%oliva%' ORDER BY id;

Consulta	Files	Resultat
`ILIKE 'oli%'`	2	Oli d'oliva verge extra 500 ml · Oli corporal d'ametlles 200 ml
`ILIKE '%oliva%'`	1	Oli d'oliva verge extra 500 ml
`ILIKE 'oliva%'`	0	—

Aquí hi ha resumit el problema sencer: buscar "oliva" per prefix no troba l'oli d'oliva, perquè la paraula és al mig. I buscar per contingut sí que el troba, però un LIKE '%…%' no pot fer servir un índex B-tree (08-04): l'única manera d'accelerar-lo és un índex GIN amb pg_trgm, de 08-03. El nivell 3 apareix quan l'usuari escriu frases, vol que "infusions" trobi "infusió", o espera resultats ordenats per rellevància: això ja no és LIKE, és cerca de text complet amb to_tsvector/to_tsquery i un índex GIN sobre el vector.

Necessitat	Eina	Índex
Autocompletar, codis, prefixos	`LIKE 'x%'`	B-tree (amb `text_pattern_ops` si la col·lació no és `C`)
Subcadena en un catàleg petit o mitjà	`ILIKE '%x%'`	GIN amb `pg_trgm`
Tolerància a errades ("oil")	`similarity()` de `pg_trgm`	GIN amb `pg_trgm`
Frases, arrels de paraula, rellevància	`to_tsvector @@ to_tsquery`	GIN sobre el `tsvector`
Catàlegs enormes, sinònims, facetes, correcció	Motor extern (Elasticsearch, OpenSearch, Meilisearch)	—

El criteri: no muntis full-text per a 19 productes, ni resolguis un cercador d'un milió d'articles amb ILIKE '%…%'. I quan facis servir ILIKE, escapa sempre % i _ a l'entrada de l'usuari: si algú busca 100%, aquest % és un comodí.

Informe de KPI en una sola fila

L'encàrrec. "Direcció vol una tira de números a dalt del panell: vendes del mes, comandes del mes, comandes per enviar, facturació total i tiquet mitjà." La temptació és llançar cinc consultes; la solució és una, amb la clàusula FILTER de 04-04, que aplica una condició diferent a cada agregat:

SELECT COUNT(DISTINCT co.id)                                                     AS comandes_total,
       COUNT(DISTINCT co.id) FILTER (WHERE co.data_comanda >= DATE '2026-02-01') AS comandes_mes,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2)        AS facturacio_total,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte))
             FILTER (WHERE co.data_comanda >= DATE '2026-02-01'), 2)             AS facturacio_mes,
       COUNT(DISTINCT co.id) FILTER (WHERE co.estat IN ('pendent','pagat'))      AS per_enviar,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte))
             / COUNT(DISTINCT co.id), 2)                                         AS tiquet_mitja
FROM   comandes       AS co
JOIN   linies_comanda AS lc ON lc.comanda_id = co.id;

comandes_total	comandes_mes	facturacio_total	facturacio_mes	per_enviar	tiquet_mitja
20	2	727.95	49.33	3	36.40

Una consulta, una fila, sis indicadors, i tots són xifres canòniques del curs: les 20 comandes, els 727,95 € de facturació de producte, els 49,33 € del febrer del 2026 i el tiquet mitjà de 36,40 €. Les 3 "per enviar" són les comandes 18 i 19 (pagat) i la 20 (pendent).

El COUNT(DISTINCT co.id) és obligatori: el JOIN amb linies_comanda multiplica cada comanda per les seves línies, i un COUNT(*) retornaria 47. I FILTER és l'estàndard SQL per a això; l'equivalent portable és SUM(CASE WHEN … THEN … END) de 06-05, més verbós i amb el parany que COUNT(CASE …) compta també els NULL si no s'escriu amb cura.

Top N i rànquing

L'encàrrec. "Els cinc productes que més facturen, amb la seva posició." És el patró de 10-03 en la seva forma més simple, ROW_NUMBER calculat en una CTE i filtrat fora:

WITH vendes AS (
    SELECT p.id AS producte_id, p.nom AS producte,
           ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio
    FROM   linies_comanda AS lc
    JOIN   productes      AS p ON p.id = lc.producte_id
    GROUP  BY p.id, p.nom)
SELECT posicio, producte, facturacio FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY facturacio DESC, producte_id) AS posicio
    FROM   vendes) AS r
WHERE  posicio <= 5 ORDER BY posicio;

posicio	producte	facturacio
1	Oli d'oliva verge extra 500 ml	109.53
2	Te verd matcha cerimonial 30 g	88.00
3	Crema facial d'àloe vera 50 ml	70.42
4	Kombutxa de gingebre 750 ml	56.43
5	Arròs integral ecològic 1 kg	54.60

Recorda les tres decisions de 10-03, perquè en producció importen: ROW_NUMBER per tallar exactament N files, RANK si els empatats a l'últim lloc han de sortir tots, i un desempat explícit a l'ORDER BY de la finestra —aquí producte_id— perquè el resultat sigui reproduïble. Si només necessites els 5 primers i no la columna de posició, un ORDER BY … LIMIT 5 sobre un índex és més barat (regla 4 de 08-04).

Detecció de buits: l'anti-join

L'encàrrec. "Quins clients no han comprat mai? Quins productes no es venen? Quantes comandes entren sense comercial?" Totes les preguntes de negoci que comencen per "què no" són el mateix patró: un anti-join, que a PostgreSQL s'escriu amb NOT EXISTS (07-03). Els tres buits deliberats de BotigaVerda, en una consulta:

SELECT (SELECT COUNT(*) FROM clients AS c
        WHERE NOT EXISTS (SELECT 1 FROM comandes AS co WHERE co.client_id = c.id))          AS clients_sense_comandes,
       (SELECT COUNT(*) FROM productes AS p
        WHERE NOT EXISTS (SELECT 1 FROM linies_comanda AS lc WHERE lc.producte_id = p.id))  AS mai_venuts,
       (SELECT COUNT(*) FROM productes AS p
        WHERE NOT EXISTS (SELECT 1 FROM ressenyes AS r WHERE r.producte_id = p.id))         AS sense_ressenya,
       (SELECT COUNT(*) FROM empleats AS e
        WHERE NOT EXISTS (SELECT 1 FROM comandes AS co WHERE co.empleat_id = e.id))         AS empleats_sense_comandes,
       (SELECT COUNT(*) FROM comandes WHERE empleat_id IS NULL)                             AS comandes_web;

clients_sense_comandes	mai_venuts	sense_ressenya	empleats_sense_comandes	comandes_web
3	3	11	5	10

Els noms darrere dels números: Núria, Hugo i Inés no han comprat mai; les espelmes de cera (estoc 0), el desodorant i les càpsules d'espirulina (descatalogades) no s'han venut mai; i els cinc empleats sense comandes són tota la direcció, logística, magatzem i anàlisi. Una variant que es demana constantment: el client inactiu, que no és el que no va comprar mai sinó el que va deixar de comprar — un anti-join amb finestra temporal:

SELECT c.id, c.nom || ' ' || c.cognoms AS client, MAX(co.data_comanda) AS ultima_comanda
FROM   clients AS c JOIN comandes AS co ON co.client_id = c.id
GROUP  BY c.id, c.nom, c.cognoms
HAVING MAX(co.data_comanda) < DATE '2026-03-01' - INTERVAL '6 months'
ORDER  BY ultima_comanda;

id	client	ultima_comanda
3	Marta Sanchis Gil	2025-04-02
8	Tiago Almeida Nunes	2025-07-15

Dos clients porten més de sis mesos sense comprar a data de l'1 de març del 2026. Fixa't en la diferència de negoci: la Núria, l'Hugo i la Inés (que no van comprar mai) són un problema d'activació; la Marta i el Tiago són un problema de retenció. La mateixa taula, dues campanyes diferents.

Compte amb NOT IN. WHERE id NOT IN (SELECT empleat_id FROM comandes) retorna zero files aquí, perquè empleat_id té nuls i NOT IN amb un NULL a la llista no és cert mai (04-03, 07-03). És l'error clàssic d'aquesta família de consultes: fes servir NOT EXISTS.

Sèries temporals sense buits

L'encàrrec. "El gràfic de vendes mensuals se salta els mesos sense comandes i l'eix surt tort." Un GROUP BY només produeix files per a les dades que existeixen. Si un mes no va tenir comandes, aquell mes no hi apareix — i un gràfic que uneix directament el punt anterior amb el següent menteix. La solució és generar el calendari i unir-hi les dades amb generate_series (06-03) i un LEFT JOIN:

WITH calendari AS (
    SELECT generate_series(DATE '2025-01-01', DATE '2026-02-01', INTERVAL '1 month')::date AS mes),
vendes AS (
    SELECT date_trunc('month', co.data_comanda)::date AS mes,
           COUNT(DISTINCT co.id) AS comandes,
           ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio
    FROM   comandes AS co JOIN linies_comanda AS lc ON lc.comanda_id = co.id
    GROUP  BY 1)
SELECT to_char(cal.mes, 'YYYY-MM')      AS mes,
       COALESCE(v.comandes, 0)          AS comandes,
       COALESCE(v.facturacio, 0.00)     AS facturacio
FROM   calendari AS cal
LEFT   JOIN vendes AS v ON v.mes = cal.mes
ORDER  BY cal.mes;

mes	comandes	facturacio
2025-01	0	0.00
2025-02	0	0.00
2025-03	2	68.80
2025-04	2	61.28
2026-01	2	75.10
2026-02	2	49.33

(6 de 14 files; entre l'abril del 2025 i el gener del 2026 la sèrie és la canònica del curs: 58,85 · 95,48 · 44,60 · 48,27 · 32,76 · 97,20 · 31,70 · 64,58.) El gener i el febrer del 2025 hi apareixen amb 0,00 € encara que no existeixi ni una comanda: la botiga ja tenia catàleg i clients, però la seva primera comanda és del 4 de març. Sense el calendari, la sèrie començaria al març i ningú no veuria els dos mesos sense vendes.

Les dues peces obligatòries són el LEFT JOIN en aquesta direcció (calendari a l'esquerra, dades a la dreta) i el COALESCE, perquè el LEFT JOIN produeix NULL, no zero — i NULL en un gràfic és un forat, no un valor baix.

Cohorts de clients

L'encàrrec. "Els clients que vam captar a la primavera compren més que els de la tardor?" Una cohort és un grup de clients que comparteixen el moment d'entrada; l'anàlisi consisteix a seguir cada grup en el temps. La forma mínima —quants de cada mes de registre van arribar a comprar— és un LEFT JOIN agregat:

WITH v AS (SELECT co.client_id, COUNT(DISTINCT co.id) AS comandes,
                  SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)) AS facturacio
           FROM   comandes AS co JOIN linies_comanda AS lc ON lc.comanda_id = co.id
           GROUP  BY co.client_id)
SELECT to_char(c.data_registre, 'YYYY-MM')       AS cohort,
       COUNT(*)                                  AS clients,
       COUNT(v.client_id)                        AS compradors,
       COALESCE(SUM(v.comandes), 0)              AS comandes,
       COALESCE(ROUND(SUM(v.facturacio), 2), 0.00) AS facturacio
FROM   clients AS c LEFT JOIN v ON v.client_id = c.id
GROUP  BY 1 ORDER BY 1;

cohort	clients	compradors	comandes	facturacio
2025-01	2	2	5	167.06
2025-02	3	3	5	147.31
2025-03	2	2	4	169.21
2025-04	2	2	3	115.47
2025-05	2	2	2	97.20
2025-06	2	1	1	31.70
2025-09	1	0	0	0.00
2026-01	1	0	0	0.00

Vuit cohorts, 15 clients, 20 comandes i 727,95 €: les columnes quadren amb el total del curs, que és la primera comprovació que cal fer sempre. I la lectura és l'esperada: les cohorts antigues tenen més comandes per client (les del gener al març del 2025 porten entre 2 i 2,5 comandes per persona) i les recents encara no han comprat — senzillament perquè han tingut menys temps. Aquest biaix és el motiu que les cohorts es comparin sempre a la mateixa edat: "comandes en els 90 dies següents a l'alta", no "comandes totals". A 11-04 es reprèn amb l'advertiment honest sobre la mida de la mostra.

Detecció i neteja de duplicats

L'encàrrec. "Màrqueting diu que hi ha clients repetits." A BotigaVerda no n'hi ha, i la raó és al CREATE TABLE de 01-06: clients.email és UNIQUE. Aquesta és la primera lliçó del cas d'ús — el duplicat es preveu amb una restricció, no es neteja amb una consulta:

SELECT lower(trim(c.email)) AS email, COUNT(*) AS vegades, string_agg(c.id::text, ', ') AS ids
FROM   clients AS c GROUP BY 1 HAVING COUNT(*) > 1;

(0 files)

El duplicat apareix quan no hi ha restricció: en una taula d'importació, en un formulari sense validar, o quan la clau natural és "el mateix client" i no "el mateix email" —la Lucía donada d'alta dues vegades amb dos correus diferents. Per a això, dues tècniques: la clau normalitzada, GROUP BY lower(unaccent(trim(nom || ' ' || cognoms))) amb el seu HAVING COUNT(*) > 1, i la semblança en lloc de la igualtat, amb similarity() de pg_trgm (08-03) sobre una autounió a.id < b.id i un llindar de partida de 0.6. Totes dues retornen 0 files sobre els 15 clients del curs, que és el que ha de passar en una taula sana.

Per a la neteja, el patró canònic és ROW_NUMBER (10-03): numerar dins de cada grup de duplicats per un criteri de "quin és el bo" —el més antic, el que té comandes— i esborrar els altres. Executa primer el SELECT, revisa les files i només llavors converteix-lo en DELETE (05-04):

WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY lower(trim(email)) ORDER BY id) AS n FROM clients)
SELECT * FROM d WHERE n > 1;

Abans d'esborrar cal reapuntar les referències: si el client duplicat té comandes, cal moure-les al supervivent amb un UPDATE, o la FK ON DELETE RESTRICT ho impedirà — i menys mal.

Auditoria: qui va canviar què i quan

L'encàrrec. "El preu de l'oli va canviar dimarts i ningú no sap qui el va tocar." Una base de dades, per omissió, no recorda: un UPDATE substitueix el valor anterior i no deixa rastre. Registrar els canvis és una decisió de disseny explícita, i hi ha tres maneres:

Enfocament	Com	Avantatge	Cost
Taula d'auditoria amb disparador	`AFTER INSERT/UPDATE/DELETE` que escriu a `auditoria_*` (10-05)	Ningú no la pot saltar: captura també els canvis fets a mà a `psql`	Escriptura extra a cada canvi
Columnes de rastre	`creat_el`, `creat_per`, `modificat_el`, `modificat_per`	Baratíssim	Només desa l'últim canvi
Versionat temporal	Una fila per versió amb `valid_des_de`/`valid_fins_a`	Història completa i consultable "a data de"	Complica totes les consultes

El de 10-05 és el primer, amb auditoria_preus capturant el valor anterior, el nou, current_user i now(). Dues advertències que només s'aprenen en producció. current_user és l'usuari de la base de dades, no el de l'aplicació: si el web es connecta amb un únic rol bv_app (11-03), totes les files diran bv_app; per saber quina persona va ser cal propagar l'usuari amb SET LOCAL app.usuari = '...' en obrir la transacció i llegir-lo amb current_setting('app.usuari', true). I una taula d'auditoria creix sense parar: planifica'n la purga o l'arxivat des del primer dia.

Exportació a un altre sistema

L'encàrrec. "Envia'm les vendes de l'any en un CSV per al gestor."

-- \copy a psql: llegeix i escriu a LA TEVA màquina, sense permisos especials (05-02)
\copy (SELECT co.id AS comanda, co.data_comanda, c.nom || ' ' || c.cognoms AS client, c.pais, co.estat, ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS import FROM comandes co JOIN clients c ON c.id = co.client_id JOIN linies_comanda lc ON lc.comanda_id = co.id WHERE co.data_comanda >= '2025-01-01' AND co.data_comanda < '2026-01-01' GROUP BY co.id, co.data_comanda, c.nom, c.cognoms, c.pais, co.estat ORDER BY co.id) TO 'vendes_2025.csv' WITH (FORMAT csv, HEADER true, DELIMITER ';', ENCODING 'UTF8')

COPY 16

Les 16 comandes del 2025. I ara els paranys, tots del món real i cap de SQL:

Separador decimal i de camps. PostgreSQL escriu 109.53 amb punt; un Excel en català espera coma i ho llegirà com cent nou mil cinc-cents cinquanta-tres. S'arregla a destinació, o a l'origen amb replace(import::text, '.', ',') — i llavors el DELIMITER no pot ser la coma. D'aquí el ';', que és el que Excel espera a l'àmbit europeu.
Codificació. UTF8 és el correcte, però l'Excel per a Windows pot necessitar el BOM o fins i tot WIN1252 per no destrossar els accents d'"Alimentació". Prova-ho amb una fila amb accents, mai amb dades netes.
Format de data i nuls. 2025-03-04 és ISO 8601 i és el correcte entre sistemes; si la destinació exigeix 04/03/2025, converteix-ho amb to_char(data, 'DD/MM/YYYY') i no confiïs en DateStyle. Un NULL surt com a camp buit: si la destinació no distingeix buit de nul, fes servir NULL 'NUL' o COALESCE.
Dades personals. Un CSV amb noms i correus deixa d'estar protegit pels permisos de la base. Exporta el mínim i consulta 11-03 abans d'enviar res amb dades de clients.

COPY ... TO (sense barra) s'executa al servidor i requereix el rol pg_write_server_files; \copy és el de psql, el que faràs servir gairebé sempre. I per al consum per part d'un altre programa, retornar JSON des de la base (10-06) sol ser millor que un CSV.

Càrrega des de fitxer amb taula de staging

L'encàrrec. "Ens han passat un CSV amb 600 clients d'una fira. Carrega'l." No carreguis mai directament sobre la taula bona: carrega en una taula de staging sense restriccions, valida-hi i mou només el que passa.

flowchart LR
    A["fitxer.csv"] -->|"\copy"| B["stg_clients<br/>sense restriccions"] --> C{"validar: format · duplicats<br/>catàlegs · ja existents"}
    C -->|"vàlides"| D["INSERT ... SELECT<br/>a clients"]
    C -->|"rebutjades"| E["informe d'errors"]

CREATE TEMP TABLE stg_clients (id INTEGER, nom TEXT, cognoms TEXT, email TEXT, pais TEXT);
-- \copy stg_clients FROM 'fira.csv' WITH (FORMAT csv, HEADER true, DELIMITER ';')

SELECT s.id, s.email, s.pais,
       CASE WHEN s.email NOT LIKE '%_@_%.%'                                        THEN 'email amb format no vàlid'
            WHEN s.pais NOT IN ('Espanya','Portugal','França')                     THEN 'país fora del catàleg'
            WHEN EXISTS (SELECT 1 FROM clients AS c
                         WHERE lower(c.email) = lower(s.email))                    THEN 'ja existeix a clients'
            WHEN COUNT(*) OVER (PARTITION BY lower(trim(s.email))) > 1             THEN 'duplicat dins del fitxer'
       END AS motiu_rebuig
FROM   stg_clients AS s ORDER BY s.id;

Sobre un fitxer de prova de sis files —dues d'elles la mateixa Lucía escrita de dues maneres, un client que ja existeix, un correu sense @, un país fora de catàleg i una alta bona—:

id	email	pais	motiu_rebuig
1	[email protected]	Espanya	ja existeix a clients
2	[email protected]	Espanya	ja existeix a clients
3	[email protected]	Espanya	ja existeix a clients
4	[email protected]	França	(null)
5	bruno.silva-example.pt	Portugal	email amb format no vàlid
6	[email protected]	Itàlia	país fora del catàleg

De sis files se'n carrega una. Aquest informe és el lliurable: qui t'ha enviat el fitxer necessita saber què no ha entrat i per què, i una càrrega que falla amb ERROR: duplicate key value a la fila 400 no l'hi diu a ningú. La inserció final és un INSERT ... SELECT amb WHERE motiu_rebuig IS NULL, o un INSERT ... ON CONFLICT DO NOTHING de 05-05 si prefereixes que el UNIQUE faci d'última xarxa — tot dins d'una transacció (09-03) perquè la càrrega sigui tot o res.

Taula resum: cas d'ús → eina → lliçó

Cas d'ús	Eina del curs	Lliçó
Llistat amb filtres opcionals i paginació	`(:p IS NULL OR col = :p)`; keyset en lloc d'`OFFSET`	02-03, 02-06, 08-04
Cerca per contingut	`ILIKE` + GIN amb `pg_trgm`; full-text si hi ha frases	04-01, 08-03
Quadre de comandament d'una fila	`COUNT`/`SUM` amb `FILTER`, `COUNT(DISTINCT …)`	04-04, 06-05
Top N i rànquing	`ROW_NUMBER`/`RANK` en CTE, filtrat fora	10-02, 10-03
Clients inactius, productes sense vendre	`NOT EXISTS` (anti-join), `LEFT JOIN … IS NULL`	03-03, 07-03
Sèrie temporal sense buits / cohorts	`generate_series` + `LEFT JOIN` + `COALESCE`; `date_trunc`	04-05, 06-03, 10-02
Duplicats: detecció i neteja	`GROUP BY … HAVING COUNT(*) > 1`, `ROW_NUMBER`, `UNIQUE`	04-06, 05-01, 10-03
Auditoria de canvis / exportació a CSV	Disparador `AFTER` + taula `auditoria_*` / `\copy … TO`	05-02, 06-03, 10-05
Càrrega des de fitxer	Taula de staging + validació + `INSERT … SELECT`	05-02, 05-05, 09-03
Informe car que es repeteix / resposta imbricada per a una API	Vista materialitzada / `jsonb_agg`	10-01, 10-06

Errors habituals i consells

Fer servir NOT IN amb una subconsulta que pot retornar NULL. Retorna zero files i sembla que "no hi ha buits": fes servir NOT EXISTS. I oblidar COUNT(DISTINCT …) en un informe amb JOIN a les línies: el JOIN multiplica, 20 comandes es converteixen en 47 files i el tiquet mitjà queda dividit per 2,35.
Pintar una sèrie temporal sense calendari. Els mesos sense dades no se salten: valen zero, i el gràfic que els omet dibuixa una tendència que no existeix. I comparar cohorts de diferent edat: la del gener porta un any comprant i la del desembre, un mes.
Carregar un CSV directament sobre la taula de producció. Una fila dolenta avorta la càrrega sencera o, pitjor, hi entra a mitges. Staging, validació i informe de rebutjos. I concatenar valors de l'usuari en construir un WHERE dinàmic: compon condicions si cal, els valors sempre són paràmetres (11-03).
Consell: comprova els totals de cada informe contra una xifra que ja coneguis. Les vuit cohorts d'aquest text sumen 727,95 € perquè ho han de sumar; si no quadra, l'error és al JOIN, no a les dades. I desa cada consulta d'informe al repositori amb un comentari que digui quina pregunta respon. És el germen de la capa semàntica d'11-04. I quan un encàrrec soni nou, busca'l a la taula de l'apartat 12: gairebé sempre és un d'aquests dotze amb un altre nom.

Exercicis

Exercici 1

La pantalla d'administració de comandes necessita un llistat amb tres filtres opcionals —estat, país del client i rang de dates— ordenat per data descendent i paginat de 10 en 10. (1) Escriu-lo amb el patró d'una sola consulta. (2) Quantes files retorna sense cap filtre i quantes amb estat = 'lliurat'? (3) Reescriu la paginació en mode keyset i explica quina columna necessites perquè sigui estable.

Exercici 2

Direcció vol una alerta de catàleg: els productes actius que no s'han venut mai o que no tenen cap ressenya, amb una columna que digui quin dels dos problemes tenen (o tots dos). (1) Escriu-la. (2) Quantes files en surten? (3) Per què el resultat canvia si fas servir INNER JOIN en lloc de NOT EXISTS?

Exercici 3

Et passen un fitxer d'altes de proveïdors amb aquestes columnes: nom, pais, email. (1) Dissenya la taula de staging i digues per què no ha de tenir restriccions. (2) Escriu les quatre validacions que hi aplicaries abans d'inserir a proveidors. (3) Què faries amb una fila el nom de la qual ja existeix però amb l'email diferent?

Solucions

Solució 1

-- 1
SELECT co.id, co.data_comanda, c.nom || ' ' || c.cognoms AS client, c.pais, co.estat
FROM   comandes AS co
JOIN   clients  AS c ON c.id = co.client_id
WHERE  (:estat IS NULL OR co.estat = :estat)
  AND  (:pais  IS NULL OR c.pais   = :pais)
  AND  (:des_de IS NULL OR co.data_comanda >= :des_de)
  AND  (:fins   IS NULL OR co.data_comanda <  :fins)
ORDER  BY co.data_comanda DESC, co.id DESC
LIMIT  10;

2. Sense filtres, 10 files (la primera pàgina de les 20 comandes); amb estat = 'lliurat', també 10, perquè n'hi ha 14 de lliurades. El total sense LIMIT seria 20 i 14. Fixa't en < :fins i no <=: el patró de rang de 08-04. 3. Keyset: WHERE (co.data_comanda, co.id) < (:ultima_data, :ultim_id) ORDER BY co.data_comanda DESC, co.id DESC LIMIT 10. Cal co.id com a desempat, perquè data_comanda no és única: sense ell, dues comandes del mateix dia es podrien repetir o perdre entre pàgines. Per això l'ORDER BY d'un keyset ha d'acabar sempre en una columna única.

Solució 2

SELECT p.id, p.nom,
       CASE WHEN sense_venda AND sense_ressenya THEN 'sense vendes i sense ressenyes'
            WHEN sense_venda                    THEN 'mai venut'
            ELSE                                     'sense ressenyes' END AS problema
FROM (SELECT p.*,
             NOT EXISTS (SELECT 1 FROM linies_comanda AS lc WHERE lc.producte_id = p.id) AS sense_venda,
             NOT EXISTS (SELECT 1 FROM ressenyes      AS r  WHERE r.producte_id  = p.id) AS sense_ressenya
      FROM   productes AS p WHERE p.actiu) AS p
WHERE  sense_venda OR sense_ressenya
ORDER  BY p.id;

2. Deu files. Els productes actius sense ressenya són 10 (els 11 sense ressenya menys les càpsules d'espirulina, que estan descatalogades i queden fora pel WHERE p.actiu), i els dos actius mai venuts —les espelmes de cera (id 13) i el desodorant (id 19)— són dins d'aquests deu, perquè tampoc no tenen ressenya. D'aquí que la resposta no sigui 12: la unió dels dos conjunts, no la suma. 3. Un INNER JOIN amb linies_comanda o amb ressenyes respon a la pregunta contrària: retorna els productes que sí que tenen vendes o ressenyes. I un LEFT JOIN … WHERE lc.id IS NULL sí que funcionaria, però produeix files intermèdies que després cal descartar i obliga a un DISTINCT; NOT EXISTS expressa la pregunta directament i el motor la resol com a anti-join (07-03, 07-05).

Solució 3

1. CREATE TEMP TABLE stg_proveidors (nom TEXT, pais TEXT, email TEXT); — tot text i sense restriccions, ni NOT NULL, ni UNIQUE, ni CHECK. El motiu: si el staging rebutja files, la càrrega falla i perds precisament la informació que necessites (quines files venien malament i per què). El staging accepta la brossa per poder-la inventariar; la taula bona és la que la rebutja. 2. (a) Obligatoris: nom i pais no nuls ni buits després del trim. (b) Format: email LIKE '%_@_%.%' o NULL —a proveidors l'email és opcional—. (c) Catàleg: pais IN ('Espanya','Portugal','França','Alemanya'), o millor contra una taula de països. (d) Duplicats: dins del fitxer amb COUNT(*) OVER (PARTITION BY lower(trim(nom))), i contra la taula real amb EXISTS. 3. És una decisió de negoci, no tècnica: pot ser el mateix proveïdor que va canviar de correu (→ upsert de 05-05) o dues empreses diferents amb un nom semblant (→ INSERT). El que no ha de fer el programa és triar en silenci: marca la fila com a "revisió manual" i que decideixi una persona. I si el nom ha de ser únic de debò, aquesta regla va en un UNIQUE sobre proveidors, no al guió de càrrega.

Conclusió

Aquest era el catàleg d'encàrrecs, i ja el tens complet:

El llistat amb filtres opcionals es resol amb (:param IS NULL OR columna = :param) a canvi d'un pla genèric, o construint el SQL a l'aplicació component condicions, mai valors. La cerca té tres nivells: prefix amb B-tree, contingut amb pg_trgm i GIN, i full-text quan hi ha frases i rellevància — buscar "oliva" per prefix no troba l'oli d'oliva.
El quadre de comandament cap en una fila i una consulta amb FILTER: 20 comandes, 727,95 €, 36,40 € de tiquet mitjà i 3 comandes per enviar; el top N és ROW_NUMBER en una CTE filtrat fora. Els buits són sempre un NOT EXISTS —3 clients sense comprar, 3 productes sense vendre, 11 sense ressenya, 10 comandes web—, i mai un NOT IN si pot haver-hi nuls. Les sèries temporals es generen amb generate_series i s'uneixen amb LEFT JOIN + COALESCE, perquè el gener del 2025 hi aparegui amb 0,00 € en lloc de desaparèixer. Les cohorts agrupen per mes d'alta i només es comparen a la mateixa edat.
Els duplicats es prevenen amb UNIQUE i es detecten amb GROUP BY … HAVING; l'auditoria viu en un disparador, amb l'advertiment que current_user no és l'usuari de la teva aplicació. L'exportació falla pel separador decimal, la codificació i el format de data, no pel SQL; i la càrrega passa sempre per una taula de staging que accepta la brossa per poder-la inventariar: de sis files d'exemple, només una era carregable.

Totes aquestes consultes funcionen. La pregunta que ve ara és una altra: podrà una altra persona mantenir-les d'aquí a un any? A la lliçó següent, Bones pràctiques, es tracta l'ofici: la nomenclatura de taules, columnes, claus i índexs, i per què l'important és ser coherent; el format que fa llegible una consulta de quaranta línies; les decisions de disseny —normalitzar, tipus restrictius, NOT NULL per defecte, restriccions amb nom— que separen una base amb la qual es pot treballar d'una que fa por tocar; el procés, amb migracions, revisions de codi i còpies de seguretat provades; i el catàleg d'antipatrons, del SELECT * en producció al "ho arreglo directament en producció".

Casos d'ús al món real

Contingut

Llistat paginat amb filtres opcionals

El patró `(:param IS NULL OR columna = :param)`

L'alternativa: construir el SQL a l'aplicació

Cerca: prefix, contingut i full-text

Informe de KPI en una sola fila

Top N i rànquing

Detecció de buits: l'anti-join

Sèries temporals sense buits

Cohorts de clients

Detecció i neteja de duplicats

Auditoria: qui va canviar què i quan

Exportació a un altre sistema

Càrrega des de fitxer amb taula de staging

Taula resum: cas d'ús → eina → lliçó

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Casos d'ús al món real

Contingut

Llistat paginat amb filtres opcionals

El patró (:param IS NULL OR columna = :param)

L'alternativa: construir el SQL a l'aplicació

Cerca: prefix, contingut i full-text

Informe de KPI en una sola fila

Top N i rànquing

Detecció de buits: l'anti-join

Sèries temporals sense buits

Cohorts de clients

Detecció i neteja de duplicats

Auditoria: qui va canviar què i quan

Exportació a un altre sistema

Càrrega des de fitxer amb taula de staging

Taula resum: cas d'ús → eina → lliçó

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

El patró `(:param IS NULL OR columna = :param)`