ALTER TABLE modifica una tabella che esiste già. Le forme che ti servono più spesso sono ADD COLUMN, RENAME COLUMN ... TO, DROP COLUMN e ALTER COLUMN ... TYPE. Puoi mettere più azioni in un'unica istruzione, separate da virgole.
Le righe esistenti ricevono 'free' in plan e NULL in signed_up. Da PostgreSQL 11, aggiungere una colonna con un default costante non riscrive la tabella, quindi è veloce anche sulle tabelle grandi. Un default volatile come clock_timestamp() invece riscrive ancora ogni riga.
Rinominare una colonna o una tabella
Una rinomina è istantanea e conserva dati, indici e vincoli. Le viste continuano a funzionare, perché fanno riferimento all'id interno della tabella, non al suo nome. I nomi scritti come testo altrove invece non si aggiornano: i corpi delle funzioni PL/pgSQL e le query dell'applicazione usano ancora il vecchio nome, quindi cercalo prima di rinominare.
Eliminare una colonna
DROP COLUMN rimuove la colonna e i suoi dati. Fallisce se una vista o una chiave esterna dipende dalla colonna. CASCADE elimina anche quelle, e IF EXISTS evita l'errore quando la colonna non c'è più.
Qui PostgreSQL non riscrive la tabella. Segna la colonna come eliminata, e lo spazio torna libero man mano che le righe vengono aggiornate o quando la tabella viene riscritta.
Cambiare il tipo di una colonna
ALTER COLUMN ... TYPE converte ogni valore. Quando non esiste un cast automatico dal vecchio tipo al nuovo, aggiungi USING con un'espressione che calcola il nuovo valore:
Senza USING, questa istruzione fallisce con column "price" cannot be cast automatically to type numeric. Se anche un solo valore non si può convertire, l'intera istruzione fallisce e la tabella resta com'era.
Quasi tutti i cambi di tipo riscrivono la tabella. Alcuni no: allungare un varchar(n), oppure passare da varchar a text.
Impostare o togliere un default e NOT NULL
SET NOT NULL scansiona la tabella e fallisce se una riga contiene un NULL, quindi riempi prima i buchi:
DROP DEFAULT e DROP NOT NULL li annullano. Un nuovo default vale solo per gli inserimenti futuri. Le righe esistenti mantengono i loro valori.
Aggiungere un vincolo
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;
Aggiungere un CHECK o una chiave esterna scansiona tutta la tabella per validarla. Su una tabella grande e molto usata, aggiungilo prima con NOT VALID, che controlla solo le righe nuove. Poi esegui ALTER TABLE orders VALIDATE CONSTRAINT total_positive;. Questo controlla le righe vecchie con un lock più debole, che lascia proseguire letture e scritture. La pagina sulle chiavi esterne usa lo stesso schema, e il riferimento di ALTER TABLE elenca ogni forma e il suo lock.
Domande frequenti
Posso cambiare l'ordine delle colonne in PostgreSQL?
No. ALTER TABLE non può spostare una colonna, e le nuove colonne vengono sempre aggiunte in fondo. Elenca le colonne nell'ordine che vuoi nella tua SELECT, oppure crea una nuova tabella con quell'ordine e copia i dati.
ALTER TABLE blocca la tabella in PostgreSQL?
Quasi tutte le forme prendono un lock ACCESS EXCLUSIVE, che blocca letture e scritture finché l'istruzione non termina. Le modifiche veloci (rinominare, eliminare una colonna, aggiungere una colonna con un default costante) lo tengono solo per un attimo. Un cambio di tipo che riscrive la tabella lo tiene per tutta la riscrittura.
Come aggiungo più colonne insieme in PostgreSQL?
Separa le azioni con delle virgole in un'unica istruzione: ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;. Tutte le azioni di un solo ALTER TABLE vengono eseguite insieme, con un solo lock.
Si può annullare un ALTER TABLE in PostgreSQL?
Sì. In PostgreSQL il DDL è transazionale, quindi un ALTER TABLE dentro BEGIN ... ROLLBACK viene annullato del tutto.