El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Instrucció INSERT

Següent ►
►

Ja tens on posar les dades. Toca posar-les. INSERT és la instrucció que afegeix files a una taula i, en aparença, és la més simple del mòdul: nom de taula, columnes, valors, punt. Però darrere d'aquesta simplicitat hi ha decisions que separen un script que aguanta cinc anys d'un que es trenca a la primera refactorització: llistar o no llistar les columnes, inserir una fila o quaranta en una sola sentència, deixar que el motor generi l'id o forçar-lo (i quin desastre silenciós provoca això segon), i com recuperar l'id acabat de crear per poder inserir les línies de la comanda que acabes de registrar.

En aquesta lliçó aprendràs les cinc formes d'INSERT, entendràs per fi què fa el bloc de setval amb què acaba botigaverda.sql, sabràs diagnosticar d'un cop d'ull cadascun dels quatre errors que un INSERT et pot donar, i acabaràs registrant una comanda completa amb les seves tres línies fent servir RETURNING, que és exactament el que fa una botiga en línia cada vegada que algú prem "Confirmar compra".

Contingut

  1. La sintaxi, i per què cal llistar sempre les columnes
  2. Inserir diverses files en una sola sentència
  3. DEFAULT i DEFAULT VALUES
  4. La columna d'identitat: ometre-la, forçar-la i setval
  5. RETURNING: recuperar el que acabes d'inserir
  6. INSERT ... SELECT: inserir el resultat d'una consulta
  7. Els quatre errors d'un INSERT i el seu diagnòstic
  8. L'ordre d'inserció quan hi ha claus foranes
  9. ON CONFLICT DO NOTHING, de passada
  10. Càrrega massiva: COPY i \copy davant d'INSERT
  11. Exemple complet: alta de producte i registre d'una comanda
  12. Errors habituals i consells
  13. Exercicis
  14. Conclusió

  1. La sintaxi, i per què cal llistar sempre les columnes

INSERT INTO taula (columna1, columna2, ...)
VALUES (valor1, valor2, ...);

Una alta de proveïdor:

INSERT INTO proveidors (nom, pais, email, actiu)
VALUES ('Cooperativa La Safor', 'Espanya', '[email protected]', TRUE);
INSERT 0 1

Aquesta sortida de psql mereix una explicació, perquè desconcerta tothom la primera vegada:

Part Significat
INSERT El tipus de sentència
0 L'OID de la fila inserida. És una relíquia: avui sempre val 0
1 El nombre de files inserides. Aquest és el número que importa

Cada vegada que executis un INSERT, mira el segon número. Si esperaves 5 files i hi diu INSERT 0 3, alguna cosa va malament.

La forma sense columnes, i per què no l'has de fer servir

SQL permet ometre la llista de columnes si dónes un valor per a totes, en l'ordre exacte de la taula:

-- ⚠️ INCORRECTA com a pràctica, encara que avui funcioni
INSERT INTO proveidors
VALUES (7, 'Cooperativa La Safor', 'Espanya', '[email protected]', TRUE);
INSERT 0 1

Funciona. I és una bomba de rellotgeria, per quatre motius:

Problema Què passa
Depens de l'ordre físic Si algú reordena les columnes amb un ALTER TABLE, els teus INSERT comencen a ficar el país a l'email
Es trenca en afegir columnes El dia que proveidors guanyi una columna telefon, tots aquests INSERT fallaran amb INSERT has more target columns than expressions… o pitjor, continuaran funcionant i desplaçaran les dades
Il·legible ('Espanya', 'ventas@…', TRUE) no diu què és què. Amb quinze columnes és directament indesxifrable
Obliga a donar l'id En llistar totes les columnes hi inclous la d'identitat, i aquí comença el problema de l'apartat 4

La regla, sense excepcions: llista sempre les columnes. Costa vint caràcters i t'estalvia una classe sencera d'errors. És, amb diferència, l'hàbit més rendible d'aquesta lliçó.

Amb la llista explícita pots a més ometre les columnes que tinguin DEFAULT o admetin nuls, i posar-les en l'ordre que vulguis:

INSERT INTO proveidors (pais, nom)
VALUES ('Portugal', 'Quinta Bio Douro');
INSERT 0 1

L'id el genera la identitat, email queda NULL (és nul·lable) i actiu pren el seu DEFAULT TRUE. Tres columnes plenes sense escriure-les.

  1. Inserir diverses files en una sola sentència

N'hi ha prou amb separar les tuples per comes:

INSERT INTO categories (nom, descripcio) VALUES
('Rebost a granel', 'Llegums, cereals i fruita seca sense envàs'),
('Nadons',          'Cura infantil amb certificació ecològica'),
('Mascotes',        'Alimentació i accessoris per a animals de companyia');
INSERT 0 3

És la forma que fa servir botigaverda.sql als seus nou blocs de càrrega, i no és només qüestió de comoditat: és moltíssim més ràpid.

Per què és més ràpid

Un INSERT de N files no costa el mateix que N INSERT d'una fila:

Cost N sentències separades 1 sentència amb N tuples
Viatges de xarxa client↔servidor N 1
Anàlisi i planificació de la consulta N vegades 1 vegada
Transaccions implícites (sense BEGIN) N confirmacions a disc 1
Comprovació de restriccions N (inevitable) N (inevitable)

Els tres primers costos són fixos per sentència i solen dominar. A la pràctica, inserir 1 000 files amb una sola sentència davant de 1 000 sentències soltes pot ser entre 10 i 50 vegades més ràpid, i la major part d'aquesta diferència ve del tercer punt: sense una transacció explícita, cada INSERT solt es confirma a disc pel seu compte.

Límits pràctics: no facis una sentència amb 100 000 tuples. El text de la consulta es torna enorme i el consum de memòria del servidor també. Lots de 500 a 5 000 files són el punt dolç habitual. I si parlem de milions, l'eina ja no és INSERT sinó COPY (apartat 10).

  1. DEFAULT i DEFAULT VALUES

La paraula clau DEFAULT pot aparèixer com a valor, i significa "fes servir el valor per omissió d'aquesta columna":

INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock, actiu, data_alta)
VALUES ('Llenties pardines ecològiques 500 g', 1, 1, 2.45, 1.05, DEFAULT, DEFAULT, DEFAULT);
INSERT 0 1

stock pren 0, actiu pren TRUE i data_alta pren CURRENT_DATE. És idèntic a haver omès les tres columnes, i de fet ometre-les és preferible: es llegeix millor. DEFAULT com a valor només resulta útil quan construeixes la sentència des d'un programa i et surt més còmode mantenir fixa la llista de columnes.

DEFAULT VALUES insereix una fila amb tots els valors per omissió:

CREATE TABLE demo_defaults (
    id    INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    estat VARCHAR(20) NOT NULL DEFAULT 'pendent',
    data  DATE        NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO demo_defaults DEFAULT VALUES;
INSERT 0 1
SELECT * FROM demo_defaults;
id estat data
1 pendent 2026-03-01

(La data serà la del dia en què ho executis.) És un cas rar, però existeix: taules de "reserva d'identificador" o d'estat inicial que s'omplen després.

  1. La columna d'identitat: ometre-la, forçar-la i setval

Aquest apartat explica, per fi, el bloc més críptic de botigaverda.sql.

El normal: ometre l'id

INSERT INTO categories (nom, descripcio)
VALUES ('Rebost a granel', 'Llegums, cereals i fruita seca sense envàs');

Com que no has donat id, PostgreSQL demana el valor següent a la seqüència associada. És el correcte i el que faràs el 99 % de les vegades.

Forçar-lo: legal amb BY DEFAULT, i perillós

Com que BotigaVerda declara les seves identitats GENERATED BY DEFAULT, pots donar l'id a mà:

INSERT INTO categories (id, nom, descripcio)
VALUES (7, 'Nadons', 'Cura infantil amb certificació ecològica');
INSERT 0 1

Funciona… i no toca la seqüència. La seqüència continua creient que l'últim valor lliurat és el 6. Així que el següent INSERT sense id:

INSERT INTO categories (nom, descripcio)
VALUES ('Mascotes', 'Alimentació i accessoris per a animals');
ERROR:  duplicate key value violates unique constraint "categories_pkey"
DETAIL:  Key (id)=(7) already exists.

La seqüència ha lliurat el 7, que ja estava ocupat. Aquest és el desajust de la seqüència, i és una de les fallades més desconcertants que existeixen: la sentència que falla és correcta i l'error apunta a un id que tu no has escrit.

flowchart TD
    A["Seqüència al 6<br/>categories té ids 1..6"] --> B["INSERT amb id = 7<br/>explícit"]
    B --> C["Taula: ids 1..7<br/>Seqüència: segueix al 6 ❌"]
    C --> D["INSERT sense id"]
    D --> E["La seqüència lliura el 7"]
    E --> F["💥 duplicate key value<br/>violates categories_pkey"]

L'arranjament: setval

setval reposiciona la seqüència. La forma robusta, que no exigeix saber com es diu la seqüència:

SELECT setval(pg_get_serial_sequence('categories', 'id'),
              (SELECT MAX(id) FROM categories));
setval
7

Ara el següent INSERT sense id demanarà el 8 i funcionarà. I aquí hi ha l'explicació de les nou línies finals de l'script del curs:

SELECT setval(pg_get_serial_sequence('categories', 'id'), (SELECT MAX(id) FROM categories));
SELECT setval(pg_get_serial_sequence('proveidors', 'id'), (SELECT MAX(id) FROM proveidors));
SELECT setval(pg_get_serial_sequence('productes',  'id'), (SELECT MAX(id) FROM productes));
...

botigaverda.sql insereix tots els id a mà —perquè les lliçons puguin parlar del "client 7" o de la "comanda 12" amb ids estables i reproduïbles—, així que en acabar la càrrega les nou seqüències estan a 1. Sense aquell bloc, el primer INSERT sense id de qualsevol taula fallaria per clau duplicada. Aquest és el detall que 01-06 va deixar anunciat "per al mòdul 5".

Tres funcions útils al voltant de les seqüències:

Funció Què fa
pg_get_serial_sequence('taula','columna') Retorna el nom de la seqüència associada ('public.categories_id_seq')
currval('sequencia') Últim valor lliurat a la teva sessió. Falla si encara no n'has demanat cap
setval('sequencia', n) Reposiciona: el valor següent serà n + 1

Com evitar tot això: declara les identitats GENERATED ALWAYS AS IDENTITY (05-01) i no les forcis mai. BotigaVerda fa servir BY DEFAULT per una raó didàctica concreta; el teu esquema no té per què.

I un detall que sorprèn: les seqüències no es desfan amb un ROLLBACK. Si una transacció consumeix el valor 21 i després avorta, aquell 21 es perd per sempre. És deliberat —altrament dues sessions concurrents s'haurien d'esperar—, i significa que els id autogenerats tenen buits. No són un comptador de files ni s'han de fer servir com a tal.

  1. RETURNING: recuperar el que acabes d'inserir

Un INSERT normal només et diu quantes files ha ficat. RETURNING, extensió de PostgreSQL, et retorna les files inserides, amb els seus valors generats inclosos:

INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock)
VALUES ('Cigrons ecològics 500 g', 1, 1, 2.60, 1.15, 140)
RETURNING id, nom, preu, stock, actiu, data_alta;
id nom preu stock actiu data_alta
21 Cigrons ecològics 500 g 2.60 140 true 2026-03-01 
INSERT 0 1

Aquí tens l'id que ha generat el motor (21, el següent al 20), l'actiu que ha posat el DEFAULT i la data_alta que ha posat CURRENT_DATE. Tot això ho hauries hagut d'anar a buscar amb un SELECT posterior… que, a més, no seria fiable en un sistema amb diversos usuaris: entre el teu INSERT i el teu SELECT hi pot haver entrat una altra comanda.

RETURNING admet el mateix que un SELECT: columnes, expressions, àlies i *.

INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock)
VALUES ('Quinoa reial ecològica 500 g', 1, 3, 6.80, 3.40, 90)
RETURNING id,
          nom,
          preu,
          cost,
          preu - cost                    AS marge,
          ROUND((preu - cost) / preu, 4) AS marge_relatiu;
id nom preu cost marge marge_relatiu
22 Quinoa reial ecològica 500 g 6.80 3.40 3.40 0.5000

Per què RETURNING és imprescindible

El cas canònic és inserir un pare i els seus fills: una comanda i les seves línies. Sense RETURNING, la seqüència seria:

  1. INSERT INTO comandes ...
  2. SELECT MAX(id) FROM comandes ← aquí hi ha la fallada
  3. INSERT INTO linies_comanda (comanda_id, ...) VALUES (aquell_id, ...)

El pas 2 és una condició de cursa: si un altre client confirma la seva compra entre el teu pas 1 i el teu pas 2, MAX(id) et retorna la comanda d'un altre i hi penges les teves línies. En una botiga amb trànsit real això no és una possibilitat teòrica: passa. RETURNING ho resol d'arrel perquè retorna la teva fila, no l'última de la taula.

Equivalents per motor

Motor Com es recupera l'id generat
PostgreSQL INSERT ... RETURNING id (també a UPDATE i DELETE)
MySQL / MariaDB SELECT LAST_INSERT_ID(); després de l'INSERT, a la mateixa connexió
SQL Server SCOPE_IDENTITY(), o la clàusula OUTPUT INSERTED.id a la mateixa sentència
SQLite SELECT last_insert_rowid(); o RETURNING des de la versió 3.35
Oracle INSERT ... RETURNING id INTO :variable (a PL/SQL o amb paràmetre de sortida)

Dos avisos sobre aquesta taula. LAST_INSERT_ID() de MySQL és per connexió, així que és segur davant d'altres usuaris però es trepitja a si mateix si fas dos INSERT seguits. I a SQL Server cal preferir SCOPE_IDENTITY() a @@IDENTITY: el segon retorna l'id generat per qualsevol àmbit, inclosos els triggers, cosa que és una font clàssica d'errors.

RETURNING funciona igual a UPDATE (05-03) i a DELETE (05-04), i allà encara és més útil: et deixa veure exactament què has canviat o què has esborrat.

  1. INSERT ... SELECT: inserir el resultat d'una consulta

En lloc de VALUES, un INSERT es pot alimentar d'una consulta. És DML pur: no estàs niant una subconsulta (això és el mòdul 7), estàs connectant la sortida d'un SELECT amb l'entrada d'un INSERT.

INSERT INTO taula_desti (col1, col2, ...)
SELECT expr1, expr2, ...
FROM ...
WHERE ...;

Les columnes s'aparellen per posició, no per nom. És la trampa clàssica: si el SELECT retorna (nom, preu) i la llista de destinació diu (preu, nom), PostgreSQL es queixarà per tipus incompatibles… o no es queixarà i et deixarà les dades creuades.

Cas 1: poblar una taula d'històric

Abans d'arxivar les comandes lliurades, en desem una foto:

CREATE TABLE comandes_historic (
    id                 INTEGER       PRIMARY KEY,
    client_id          INTEGER       NOT NULL,
    data_comanda       DATE          NOT NULL,
    estat              VARCHAR(20)   NOT NULL,
    despeses_enviament NUMERIC(10,2) NOT NULL,
    data_arxiu         DATE          NOT NULL DEFAULT CURRENT_DATE
);
CREATE TABLE
INSERT INTO comandes_historic (id, client_id, data_comanda, estat, despeses_enviament)
SELECT co.id,
       co.client_id,
       co.data_comanda,
       co.estat,
       co.despeses_enviament
FROM comandes AS co
WHERE co.estat = 'lliurat';
INSERT 0 14

14 files, les 14 comandes lliurades. data_arxiu no apareix a la llista de destinació, així que pren el seu DEFAULT CURRENT_DATE a totes.

Cas 2: duplicar el catàleg d'un proveïdor

El proveïdor 5 (EcoNordic Supplies) està inactiu. Verde Atlántico (proveïdor 3) s'ofereix a subministrar els mateixos articles amb un 8 % de recàrrec al cost. En lloc de teclejar quatre altes:

INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock, actiu, data_alta)
SELECT p.nom || ' (Verde Atlántico)',
       p.categoria_id,
       3,
       p.preu,
       ROUND(p.cost * 1.08, 2),
       0,
       TRUE,
       DATE '2026-03-01'
FROM productes AS p
WHERE p.proveidor_id = 5
ORDER BY p.id;
INSERT 0 4
SELECT id, nom, categoria_id, proveidor_id, preu, cost, stock
FROM productes
WHERE id > 20
ORDER BY id;
id nom categoria_id proveidor_id preu cost stock
21 Detergent ecològic concentrat 1 L (Verde Atlántico) 3 3 11.20 6.48 0
22 Espelmes de cera de soja (pack 2) (Verde Atlántico) 3 3 13.75 7.45 0
23 Raspall de dents de bambú (Verde Atlántico) 5 3 3.50 1.30 0
24 Càpsules d'espirulina 120 u (Verde Atlántico) 6 3 16.40 9.40 0

Quatre altes amb una sentència. Fixa't en el que fa el SELECT: transforma mentre copia. Concatena un sufix al nom, substitueix el proveidor_id per una constant, recalcula el cost i posa l'estoc a zero. Aquest és el veritable valor d'INSERT ... SELECT: no és copiar, és copiar transformant.

Comprovació dels costos, arrodonits a dos decimals: 6,00 × 1,08 = 6,48; 6,90 × 1,08 = 7,452 → 7,45; 1,20 × 1,08 = 1,296 → 1,30; 8,70 × 1,08 = 9,396 → 9,40.

Cas 3: INSERT ... SELECT amb LIMIT

Tot el que saps del mòdul 2 serveix aquí, ORDER BY i LIMIT inclosos:

CREATE TEMP TABLE top_productes (
    posicio INTEGER,
    id      INTEGER,
    nom     VARCHAR(150),
    preu    NUMERIC(10,2)
);

INSERT INTO top_productes (posicio, id, nom, preu)
SELECT ROW_NUMBER() OVER (ORDER BY p.preu DESC, p.id),
       p.id,
       p.nom,
       p.preu
FROM productes AS p
ORDER BY p.preu DESC, p.id
LIMIT 5;
INSERT 0 5
SELECT * FROM top_productes ORDER BY posicio;
posicio id nom preu
1 15 Te verd matcha cerimonial 30 g 22.00
2 6 Crema facial d'àloe vera 50 ml 18.90
3 20 Càpsules d'espirulina 120 u 16.40
4 8 Oli corporal d'ametlles 200 ml 14.25
5 13 Espelmes de cera de soja (pack 2) 13.75

(ROW_NUMBER() és una funció de finestra del mòdul 10; aquí només numera les files del rànquing.)

I compte amb un matís: ORDER BY dins d'un INSERT ... SELECT no garanteix l'ordre físic de les files a la taula destinació, perquè en el model relacional una taula és un conjunt sense ordre (01-05). Només determina quines files entren quan hi ha LIMIT. Per llegir-les ordenades necessitaràs un ORDER BY al SELECT, sempre.

  1. Els quatre errors d'un INSERT i el seu diagnòstic

Un INSERT pot fallar per quatre motius, un per cada tipus de restricció. Conèixer els missatges literals t'estalvia hores.

7.1. Violació de NOT NULL

INSERT INTO productes (nom, categoria_id, preu)
VALUES (NULL, 1, 4.50);
ERROR:  null value in column "nom" of relation "productes" violates not-null constraint
DETAIL:  Failing row contains (23, null, 1, null, 4.50, null, 0, t, 2026-03-01).

El DETAIL et mostra la fila completa tal com hauria quedat, amb els valors per omissió ja aplicats. És molt útil: allà veus quines columnes s'han omplert soles.

7.2. Violació d'UNIQUE (o de la PK)

INSERT INTO categories (nom, descripcio)
VALUES ('Begudes', 'Duplicada');
ERROR:  duplicate key value violates unique constraint "categories_nom_key"
DETAIL:  Key (nom)=(Begudes) already exists.

El nom de la restricció et diu quina columna és. Si fos categories_pkey, seria l'id: probablement el desajust de seqüència de l'apartat 4.

7.3. Violació de CHECK

INSERT INTO ressenyes (producte_id, client_id, puntuacio, comentari, data)
VALUES (1, 14, 7, 'Genial', '2026-03-01');
ERROR:  new row for relation "ressenyes" violates check constraint "ressenyes_puntuacio_check"
DETAIL:  Failing row contains (13, 1, 14, 7, Genial, 2026-03-01).

El nom de la restricció identifica la regla. Aquí és la puntuació d'1 a 5, i per això 05-01 insistia tant a anomenar-les: chk_ressenyes_puntuacio hauria estat igual de clar; ressenyes_check1 no ho hauria estat.

7.4. Violació de FOREIGN KEY

INSERT INTO comandes (client_id, data_comanda, estat, metode_pagament, despeses_enviament)
VALUES (999, '2026-03-01', 'pendent', 'targeta', 4.95);
ERROR:  insert or update on table "comandes" violates foreign key constraint "comandes_client_id_fkey"
DETAIL:  Key (client_id)=(999) is not present in table "clients".

is not present in table és la signatura inconfusible d'una FK trencada: has referenciat una cosa que no existeix.

Taula de diagnòstic ràpid

Fragment del missatge Restricció trencada Què mirar
violates not-null constraint NOT NULL Falta un valor obligatori; mira'l al DETAIL
duplicate key value violates unique constraint "..._pkey" PRIMARY KEY Estàs forçant l'id? Seqüència desajustada? → setval
duplicate key value violates unique constraint "..._key" UNIQUE Ja existeix una fila amb aquell valor. Volies un UPSERT? → 05-05
violates check constraint CHECK Valor fora de domini o de rang. El nom et diu quin
violates foreign key constraint + is not present in table FOREIGN KEY El pare no existeix. Ordre d'inserció? → apartat 8
violates foreign key constraint + is still referenced from table FOREIGN KEY Això no és un INSERT: és un DELETE → 05-04
column "x" of relation "y" does not exist — Errada al nom de columna. \d y
INSERT has more expressions than target columns — Descompensació entre la llista de columnes i la de valors
invalid input syntax for type numeric: "12,50" — Coma decimal en lloc de punt (01-03)

Un detall important: quan un INSERT multifila falla, no s'insereix cap fila. Una sentència és atòmica: o entra sencera o no entra res. Si insereixes 500 tuples i la 337 viola un CHECK, les 499 restants tampoc no es desen.

  1. L'ordre d'inserció quan hi ha claus foranes

La integritat referencial imposa el mateix ordre que imposava a CREATE TABLE a 05-01: primer els pares, després els fills.

flowchart TD
    A["1 · categories<br/>proveidors"] --> B["2 · productes"]
    A2["1 · clients<br/>empleats"] --> C["3 · comandes"]
    B --> D["4 · linies_comanda"]
    C --> D
    B --> E["4 · ressenyes"]
    A2 --> E
    C --> F["4 · devolucions"]

Prova de saltar-te'l i veuràs per què:

-- ⚠️ INCORRECTA: la categoria 9 encara no existeix
INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock)
VALUES ('Pa d''espelta congelat', 9, 1, 3.20, 1.60, 40);
ERROR:  insert or update on table "productes" violates foreign key constraint "productes_categoria_id_fkey"
DETAIL:  Key (categoria_id)=(9) is not present in table "categories".

Primer la categoria, després el producte:

-- ✅ CORRECTA
INSERT INTO categories (id, nom, descripcio)
VALUES (9, 'Fleca', 'Pa i brioixeria ecològica congelada');

INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock)
VALUES ('Pa d''espelta congelat', 9, 1, 3.20, 1.60, 40);
INSERT 0 1
INSERT 0 1

Les relacions reflexives tenen un matís propi. clients.referit_per_id apunta a la mateixa taula, així que qui refereix ha d'existir abans que el referit. A l'script del curs funciona perquè els clients estan ordenats per id i cap no és referit per un altre d'id més gran. Si no fos així, tindries dues opcions: inserir primer amb referit_per_id a NULL i actualitzar-ho després (05-03), o fer servir una restricció diferida (DEFERRABLE INITIALLY DEFERRED), que posposa la comprovació al final de la transacció. Aquesta última pertany de ple al model transaccional del mòdul 9.

  1. ON CONFLICT DO NOTHING, de passada

Si intentes inserir una fila que viola un UNIQUE, l'INSERT falla. De vegades el que vols és que no falli i simplement no faci res:

INSERT INTO categories (nom, descripcio)
VALUES ('Begudes', 'Intent de duplicat')
ON CONFLICT DO NOTHING;
INSERT 0 0

Zero files inserides, zero errors. És tremendament útil per a scripts que han de poder reexecutar-se (càrregues de dades mestres, llavors de desenvolupament).

La seva germana gran, ON CONFLICT ... DO UPDATE, resol el clàssic "insereix-lo si no existeix i actualitza'l si existeix". Això és l'UPSERT, i té lliçó pròpia: 05-05.

  1. Càrrega massiva: COPY i \copy davant d'INSERT

Per a volums grans, INSERT no és l'eina. PostgreSQL té COPY, dissenyada específicament per moure dades entre un fitxer i una taula:

-- COPY s'executa al SERVIDOR: la ruta és del servidor
-- i cal ser superusuari o tenir el rol pg_read_server_files
COPY productes (nom, categoria_id, proveidor_id, preu, cost, stock)
FROM '/var/lib/postgresql/import/productes.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');
COPY 4500

\copy és l'ordre equivalent de psql, que llegeix el fitxer a la teva màquina i l'envia per la connexió. No necessita permisos especials i és el que faràs servir gairebé sempre:

botigaverda=> \copy productes (nom, categoria_id, proveidor_id, preu, cost, stock) FROM 'productes.csv' WITH (FORMAT csv, HEADER true)
COPY 4500

També funciona en sentit invers, per exportar:

botigaverda=> \copy (SELECT id, nom, preu FROM productes ORDER BY id) TO 'cataleg.csv' WITH (FORMAT csv, HEADER true)
COPY 20

Comparativa honesta:

INSERT multifila COPY / \copy
Origen de les dades Text SQL Fitxer CSV/text o flux
Velocitat relativa Referència 5 a 20 vegades més ràpid
Anàlisi de la sentència Una vegada per sentència Cap: és un protocol binari/textual
Comprovació de restriccions Sí Sí, totes
ON CONFLICT Sí No
Transformació de dades Sí (amb INSERT ... SELECT) No: entra tal qual
Si falla una fila Falla la sentència sencera Falla la càrrega sencera
Estàndard SQL Sí No: és de PostgreSQL

La regla: desenes o centenars de files → INSERT multifila. Milers o milions → COPY. I si necessites transformar mentre carregues, el patró professional és de dos passos: COPY a una taula de staging sense restriccions, i d'allà INSERT ... SELECT transformant cap a la taula definitiva. Aquell patró reapareixerà a 05-05 i al mòdul 11.

Equivalents per motor: MySQL té LOAD DATA INFILE, SQL Server té BULK INSERT i la utilitat bcp, Oracle té SQL*Loader i les external tables, i SQLite té l'ordre .import de la seva consola. Cap no és compatible amb els altres.

  1. Exemple complet: alta de producte i registre d'una comanda

Tanquem amb el que fa una botiga de debò. Treballem sobre la base acabada de carregar.

11.1. Donar d'alta un producte nou

Huerta del Turia (proveïdor 1) incorpora cigrons a la categoria Alimentació:

INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock)
VALUES ('Cigrons ecològics 500 g', 1, 1, 2.60, 1.15, 140)
RETURNING id, nom, preu, cost, stock, actiu, data_alta;
id nom preu cost stock actiu data_alta
21 Cigrons ecològics 500 g 2.60 1.15 140 true 2026-03-01 
INSERT 0 1

Tres columnes omeses i tres columnes plenes soles: id per la identitat, actiu i data_alta pels seus DEFAULT. Exactament la feina que 05-01 va deixar declarada al DDL.

11.2. Registrar una comanda amb les seves línies

Pau Llorens Vidal (client 6) truca per telèfon. L'atén Óscar Peris Blasco (empleat 4). Vol dos olis, una mel i tres infusions, amb ports de 4,95 €.

Pas 1: la capçalera, amb RETURNING.

INSERT INTO comandes (client_id, empleat_id, data_comanda, estat, metode_pagament, despeses_enviament)
VALUES (6, 4, DATE '2026-03-02', 'pendent', 'targeta', 4.95)
RETURNING id, client_id, empleat_id, data_comanda, estat, despeses_enviament;
id client_id empleat_id data_comanda estat despeses_enviament
21 6 4 2026-03-02 pendent 4.95 
INSERT 0 1

La comanda és la 21. Aquell número no l'hem triat nosaltres: ens l'ha dit el motor.

Pas 2: les tres línies, en una sola sentència.

Els preus es copien del catàleg en el moment de la venda —és la desnormalització deliberada de preu_unitari (01-05)— i aquí això significa: 12,50 €, 9,75 € i 3,25 €.

INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari, descompte) VALUES
(21,  1, 2, 12.50, 0.00),
(21,  3, 1,  9.75, 0.00),
(21, 14, 3,  3.25, 0.00)
RETURNING id, comanda_id, producte_id, quantitat, preu_unitari,
          quantitat * preu_unitari * (1 - descompte) AS import;
id comanda_id producte_id quantitat preu_unitari import
48 21 1 2 12.50 25.0000
49 21 3 1 9.75 9.7500
50 21 14 3 3.25 9.7500 
INSERT 0 3

Les línies 48, 49 i 50, a continuació de les 47 que ja hi havia.

Pas 3: comprovar la comanda completa.

SELECT co.id                          AS comanda,
       c.nom || ' ' || c.cognoms      AS client,
       e.nom || ' ' || e.cognoms      AS comercial,
       COUNT(*)                       AS linies,
       SUM(lc.quantitat)              AS unitats,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS productes,
       co.despeses_enviament          AS ports,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)) + co.despeses_enviament, 2) AS total
FROM comandes       AS co
JOIN linies_comanda AS lc ON lc.comanda_id = co.id
JOIN clients        AS c  ON co.client_id  = c.id
JOIN empleats       AS e  ON co.empleat_id = e.id
WHERE co.id = 21
GROUP BY co.id, c.nom, c.cognoms, e.nom, e.cognoms, co.despeses_enviament;
comanda client comercial linies unitats productes ports total
21 Pau Llorens Vidal Óscar Peris Blasco 3 6 44.50 4.95 49.45

44,50 € de producte més 4,95 € de ports: 49,45 €. Tot el mòdul 4 aplicat a una fila que has creat tu.

El que falta i per què. Un sistema real faria aquestes dues insercions dins d'una mateixa transacció, perquè sigui impossible que quedi una capçalera sense línies si alguna cosa falla enmig. I descomptaria l'estoc dels tres productes. El primer el començaràs a fer servir a la lliçó següent i l'estudiaràs a fons al mòdul 9; el segon és un UPDATE, i també és de la lliçó següent.

Recorda recarregar. Si has executat aquests exemples, la teva base ja no coincideix amb la del curs: hi ha 21 o més productes, 21 comandes i 50 línies. Torna a llançar botigaverda.sql abans de continuar.

Errors habituals i consells

  • Ometre la llista de columnes. Funciona avui i es trenca el dia que algú toqui la taula. Llista sempre les columnes.
  • Inserir l'id a mà en una identitat BY DEFAULT. La seqüència es queda enrere i el següent INSERT automàtic falla amb duplicate key ... _pkey. Arregla-ho amb setval o, millor, no ho facis.
  • Fer servir SELECT MAX(id) per saber què acabes d'inserir. És una condició de cursa. Fes servir RETURNING.
  • Aparellar malament les columnes en un INSERT ... SELECT. S'aparellen per posició. Llegeix-les dues vegades.
  • Inserir fills abans que pares. is not present in table. Pares primer, sempre.
  • Creure que un INSERT multifila insereix el que pot. És atòmic: si falla una tupla, no n'entra cap.
  • Confondre NULL amb DEFAULT. VALUES (NULL) desa NULL (o falla amb NOT NULL); VALUES (DEFAULT) aplica el valor per omissió. Ometre la columna equival a això segon.
  • Escriure els decimals amb coma. 12,50 no és un número en SQL: és invalid input syntax for type numeric (01-03).
  • Carregar un milió de files amb INSERT. Per a això hi ha COPY. I si necessites transformar, COPY a staging i després INSERT ... SELECT.
  • Copiar productes.preu a preu_unitari des de la taula en lloc de fixar-lo. El preu de la línia és el del moment de la venda; si el lligues al catàleg, demà canviaran les teves factures antigues.
  • Consell: mira sempre el segon número d'INSERT 0 N. És l'únic que et diu si ha fet el que esperaves.
  • Consell: fes servir RETURNING fins i tot quan no el necessitis. Veure la fila resultant amb els seus DEFAULT aplicats és la manera més ràpida de comprovar que el DDL fa el que et penses.
  • Consell: per a dades mestres reexecutables, ON CONFLICT DO NOTHING. Converteix un script fràgil en un d'idempotent.

Exercicis

Exercici 1

BotigaVerda incorpora un proveïdor i dos productes seus. Escriu les sentències necessàries, en l'ordre correcte, complint aquests requisits:

  1. Alta del proveïdor Cooperativa La Safor, d'Espanya, amb email [email protected], actiu. Recupera'n l'id amb RETURNING.
  2. Alta de dos productes seus en una sola sentència, tots dos de la categoria Alimentació (id 1):
    • Ametlla marcona ecològica 250 g, preu 7,90 €, cost 4,20 €, stock 60.
    • Taronges de València ecològiques 5 kg, preu 11,50 €, cost 6,00 €, stock 35.
    • En tots dos, actiu i data_alta han de quedar en els seus valors per omissió, sense escriure'ls.
  3. Una consulta que mostri el proveïdor nou amb els seus dos productes i el marge de cadascun.

Exercici 2

Prediu què passa amb cadascuna d'aquestes sentències sobre la base acabada de recarregar, i si falla, digues quina restricció es trenca i quin seria el missatge. Després comprova-ho.

-- a)
INSERT INTO linies_comanda (comanda_id, producte_id, quantitat, preu_unitari)
VALUES (1, 5, 0, 1.95);

-- b)
INSERT INTO clients (nom, cognoms, email, pais)
VALUES ('Lucía', 'Martínez Soler', '[email protected]', 'Espanya');

-- c)
INSERT INTO empleats (nom, cognoms, carrec, cap_id, data_contractacio)
VALUES ('Nerea', 'Blasco Tur', 'Comercial', 2, '2026-03-01');

-- d)
INSERT INTO comandes (client_id, data_comanda, estat, metode_pagament)
VALUES (14, '2026-03-01', 'preparant', 'targeta');

-- e)
INSERT INTO devolucions (comanda_id, motiu, data, import)
VALUES (21, 'Producte defectuós', '2026-03-05', 12.00);

Exercici 3

L'equip d'anàlisi vol una taula de resum mensual de vendes del 2025 per no haver de recalcular-la a cada informe.

  1. Crea la taula vendes_mensuals amb: mes (text AAAA-MM, clau primària), comandes (enter, obligatori), linies (enter, obligatori), unitats (enter, obligatori), facturacio (NUMERIC(10,2), obligatori, no negatiu) i data_calcul (DATE, obligatori, per omissió avui).
  2. Omple-la amb un INSERT ... SELECT a partir de les comandes del 2025.
  3. Comprova el resultat i verifica que la suma de la columna facturacio coincideix amb la facturació de producte del 2025.

Solucions

Solució 1

-- 1) El proveïdor primer: és el pare
INSERT INTO proveidors (nom, pais, email, actiu)
VALUES ('Cooperativa La Safor', 'Espanya', '[email protected]', TRUE)
RETURNING id, nom, pais, email, actiu;
id nom pais email actiu
6 Cooperativa La Safor Espanya [email protected] true 
INSERT 0 1
-- 2) Els dos productes, en una sola sentència
INSERT INTO productes (nom, categoria_id, proveidor_id, preu, cost, stock) VALUES
('Ametlla marcona ecològica 250 g',       1, 6,  7.90, 4.20, 60),
('Taronges de València ecològiques 5 kg', 1, 6, 11.50, 6.00, 35)
RETURNING id, nom, preu, cost, stock, actiu, data_alta;
id nom preu cost stock actiu data_alta
21 Ametlla marcona ecològica 250 g 7.90 4.20 60 true 2026-03-01
22 Taronges de València ecològiques 5 kg 11.50 6.00 35 true 2026-03-01 
INSERT 0 2
-- 3) Comprovació
SELECT pr.id  AS proveidor_id,
       pr.nom AS proveidor,
       p.id   AS producte_id,
       p.nom  AS producte,
       p.preu,
       p.cost,
       p.preu - p.cost AS marge
FROM productes  AS p
JOIN proveidors AS pr ON p.proveidor_id = pr.id
WHERE pr.nom = 'Cooperativa La Safor'
ORDER BY p.id;
proveidor_id proveidor producte_id producte preu cost marge
6 Cooperativa La Safor 21 Ametlla marcona ecològica 250 g 7.90 4.20 3.70
6 Cooperativa La Safor 22 Taronges de València ecològiques 5 kg 11.50 6.00 5.50

Tres decisions que calia prendre bé: l'ordre (proveïdor abans que productes, perquè és el pare de la FK), una sola sentència per als dos productes, i ometre actiu i data_alta en lloc d'escriure'ls. I un detall: l'id 6 del proveïdor no s'ha escrit enlloc; s'ha recuperat amb RETURNING i s'ha fet servir a l'INSERT següent. En una aplicació real, aquell valor viatjaria en una variable.

Solució 2

a) Falla. CHECK de quantitat:

ERROR:  new row for relation "linies_comanda" violates check constraint "linies_comanda_quantitat_check"
DETAIL:  Failing row contains (48, 1, 5, 0, 1.95, 0.00).

CHECK (quantitat > 0): una línia de zero unitats no és una venda. Fixa't que descompte sí que s'ha omplert amb el seu DEFAULT 0.00 abans de comprovar la restricció.

b) Falla. UNIQUE de l'email:

ERROR:  duplicate key value violates unique constraint "clients_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

És la protecció de la clau natural de 01-05. L'id seria diferent, però l'email és únic: la base impedeix registrar dues vegades la mateixa persona. Aquest és exactament l'escenari que resoldrà l'UPSERT de 05-05.

c) Funciona.

INSERT 0 1

Es crea l'empleada 9, amb cap_id 2 (Andrés Company Talens, que existeix), salari a NULL (la columna és nul·lable) i ciutat a NULL. No es viola cap restricció: salari no és obligatori i el seu CHECK (salari >= 0) dóna UNKNOWN amb un nul, cosa que es considera satisfeta (05-01).

d) Falla. CHECK de l'estat:

ERROR:  new row for relation "comandes" violates check constraint "comandes_estat_check"
DETAIL:  Failing row contains (21, 14, null, 2026-03-01, preparant, targeta, 0.00).

'preparant' no és al domini ('pendent','pagat','enviat','lliurat','cancellat'). Observa el DETAIL: empleat_id ha quedat a NULL (comanda web) i despeses_enviament ha pres el seu DEFAULT 0.00. La fila estava gairebé bé.

e) Falla. FOREIGN KEY:

ERROR:  insert or update on table "devolucions" violates foreign key constraint "devolucions_comanda_id_fkey"
DETAIL:  Key (comanda_id)=(21) is not present in table "comandes".

Sobre la base acabada de recarregar només hi ha 20 comandes. La comanda 21 és la que vam crear a l'apartat 11, no la que hi ha ara. És un recordatori de per què convé recarregar l'script entre blocs d'exercicis.

Solució 3

-- 1) La taula
CREATE TABLE vendes_mensuals (
    mes          CHAR(7)       NOT NULL,
    comandes     INTEGER       NOT NULL,
    linies       INTEGER       NOT NULL,
    unitats      INTEGER       NOT NULL,
    facturacio   NUMERIC(10,2) NOT NULL,
    data_calcul  DATE          NOT NULL DEFAULT CURRENT_DATE,

    CONSTRAINT pk_vendes_mensuals     PRIMARY KEY (mes),
    CONSTRAINT chk_vendes_facturacio  CHECK (facturacio >= 0)
);
CREATE TABLE
-- 2) La càrrega
INSERT INTO vendes_mensuals (mes, comandes, linies, unitats, facturacio)
SELECT TO_CHAR(co.data_comanda, 'YYYY-MM'),
       COUNT(DISTINCT co.id),
       COUNT(*),
       SUM(lc.quantitat),
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2)
FROM linies_comanda AS lc
JOIN comandes AS co ON lc.comanda_id = co.id
WHERE co.data_comanda >= DATE '2025-01-01'
  AND co.data_comanda <  DATE '2026-01-01'
GROUP BY TO_CHAR(co.data_comanda, 'YYYY-MM');
INSERT 0 10
-- 3) Comprovació
SELECT mes, comandes, linies, unitats, facturacio
FROM vendes_mensuals
ORDER BY mes;
mes comandes linies unitats facturacio
2025-03 2 5 10 68.80
2025-04 2 5 14 61.28
2025-05 2 5 6 58.85
2025-06 2 5 14 95.48
2025-07 1 3 9 44.60
2025-08 1 2 3 48.27
2025-09 1 2 13 32.76
2025-10 2 5 13 97.20
2025-11 1 3 6 31.70
2025-12 2 5 7 64.58 
SELECT SUM(facturacio) AS total_2025,
       SUM(comandes)   AS comandes_2025,
       SUM(linies)     AS linies_2025,
       SUM(unitats)    AS unitats_2025
FROM vendes_mensuals;
total_2025 comandes_2025 linies_2025 unitats_2025
603.52 16 40 95

Deu mesos, 16 comandes i 603,52 € de facturació de producte el 2025. Les altres 4 comandes i 124,43 € corresponen al 2026: 603,52 + 124,43 = 727,95 €, la xifra canònica del mòdul 4. Quadra.

Dues observacions sobre el disseny d'aquesta taula. La primera: mes com a CHAR(7) en format AAAA-MM és clau primària natural i ordena alfabèticament igual que cronològicament, que és just la raó per la qual aquell format es fa servir tant. La segona, més important: aquesta taula és un agregat precalculat, és a dir, desnormalització deliberada (01-05, secció 8). El seu gran problema és la sincronització: si demà entra una comanda amb data del 2025, la taula queda obsoleta i ningú no se n'assabenta. Les solucions —vistes materialitzades i triggers— són del mòdul 10.

Conclusió

INSERT és la porta d'entrada de les dades, i ja la domines:

  • La sintaxi INSERT INTO taula (columnes) VALUES (...), i la regla que no admet excepcions: llista sempre les columnes. La forma sense llista depèn de l'ordre físic de la taula i es trenca tan bon punt algú la toca.
  • La sortida INSERT 0 N: el 0 és una relíquia, la N és el que importa.
  • Diverses files en una sentència, que és el que fa l'script del curs i el que has de fer tu: entre 10 i 50 vegades més ràpid, perquè el cost dominant és fix per sentència.
  • DEFAULT com a valor i DEFAULT VALUES per a una fila sencera per omissió; i l'equivalència entre posar DEFAULT i simplement ometre la columna.
  • La columna d'identitat: ometre-la és el normal, forçar-la és legal amb BY DEFAULT i desajusta la seqüència, i l'arranjament és setval(pg_get_serial_sequence(...), MAX(id)) — que és exactament el que fan les nou línies finals de botigaverda.sql. Les seqüències no es desfan amb ROLLBACK: els id tenen buits i no compten files.
  • RETURNING, l'extensió que retorna la fila inserida amb els seus valors generats, i que elimina la condició de cursa de SELECT MAX(id). Amb els seus equivalents per motor: LAST_INSERT_ID(), SCOPE_IDENTITY(), OUTPUT.
  • INSERT ... SELECT per poblar històrics i duplicar catàlegs transformant mentre copia; les columnes s'aparellen per posició.
  • Els quatre errors d'un INSERT, amb el seu missatge literal i la seva taula de diagnòstic; i el fet que un INSERT multifila és atòmic.
  • L'ordre d'inserció: pares abans que fills, sempre.
  • COPY i \copy per a càrrega massiva, de 5 a 20 vegades més ràpids, i el patró professional de staging + INSERT ... SELECT.
  • I el cas complet: alta de producte i registre de la comanda 21 amb les seves tres línies i els seus 49,45 € de total, encadenant dos INSERT amb RETURNING.

Saps crear taules i omplir-les. El que encara no saps és corregir. A la lliçó següent, Instrucció UPDATE, canvia el to: fins ara, un error teu creava una fila de més que podies esborrar; a partir d'ara, un error teu pot sobreescriure vint files correctes i deixar-te sense manera de saber què hi havia abans. Veuràs el protocol professional perquè això no passi —escriure primer el SELECT, comptar les files, i només llavors convertir-lo en UPDATE—, l'hàbit de treballar amb BEGIN i ROLLBACK com a xarxa de seguretat, i tot el que UPDATE sap fer: diverses columnes alhora, càlculs sobre el valor anterior, actualitzacions a partir d'una altra taula amb FROM, i RETURNING per veure exactament què has canviat.

◄ Anterior
◄
Següent ►
►

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 Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2026. Tots els drets reservats