Menu

Chiave primaria PostgreSQL: IDENTITY, SERIAL e UUID

Come definire una chiave primaria in PostgreSQL e come generarla: colonne identity, il vecchio SERIAL e gli UUID con gen_random_uuid(). Più chiavi composte e il reset di una sequenza.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

Una chiave primaria è la colonna, o l'insieme di colonne, che identifica ogni riga. I suoi valori devono essere univoci e non NULL, e una tabella può averne una sola. In PostgreSQL di solito lasci che sia il database a generarla con una colonna identity: id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

Gli id sono 1 e 2. Definire la chiave crea anche un indice univoco su di essa, chiamato users_pkey.

Colonne identity: ALWAYS vs BY DEFAULT

Le colonne identity sono il modo standard SQL per numerare le righe, disponibile da PostgreSQL 10. Esistono in due forme:

  • GENERATED ALWAYS AS IDENTITY rifiuta un valore esplicito nell'INSERT, a meno che l'insert non dica OVERRIDING SYSTEM VALUE. È la scelta predefinita più sicura.
  • GENERATED BY DEFAULT AS IDENTITY usa il valore generato solo quando non ne fornisci uno tu.

Con BY DEFAULT, se inserisci id tuoi la sequenza resta indietro e il prossimo id generato va in collisione. Dopo aver importato righe con id espliciti, sposta la sequenza oltre il più alto con setval:

Senza la riga con setval, l'ultimo insert riceverebbe l'id 1 e fallirebbe con un errore di chiave duplicata. Con quella riga, la nuova riga riceve 4.

SERIAL vs identity

serial (4 byte) e bigserial (8 byte) non sono veri tipi. Sono una scorciatoia che crea una sequenza e imposta come valore predefinito della colonna il suo nextval():

La colonna è un semplice integer con il valore predefinito nextval('legacy_id_seq'::regclass). Niente impedisce a un insert di scrivere un id suo, e la sequenza ha bisogno di permessi propri. Le colonne identity risolvono entrambe le cose, quindi preferiscile nelle tabelle nuove. serial funziona ancora, e lo troverai negli schemi e nei tutorial più vecchi.

Usa bigint (o bigserial) per le tabelle che possono crescere. Una chiave integer si esaurisce dopo circa 2,1 miliardi di valori.

Chiavi primarie UUID

gen_random_uuid() restituisce un UUID casuale versione 4. È integrato da PostgreSQL 13, quindi non serve nessuna estensione:

Ogni esecuzione stampa id diversi, di 36 caratteri ciascuno in forma di testo. Il tipo uuid li memorizza in 16 byte, quindi usalo al posto di text. PostgreSQL 18 aggiunge uuidv7(), che genera UUID ordinati nel tempo che si inseriscono nell'ordine dell'indice. Su PostgreSQL 17, generali nella tua applicazione.

Chiavi primarie composte

Una chiave può comprendere più colonne, scritta su una riga a parte in CREATE TABLE. È la chiave naturale per una tabella di collegamento, dove ogni coppia può comparire una sola volta:

La coppia ripetuta (1, 10) viene saltata, quindi il conteggio è 3. Per aggiungere una chiave a una tabella esistente usa ALTER TABLE t ADD PRIMARY KEY (col). La pagina su ALTER TABLE copre le altre modifiche. Le altre tabelle puntano alla chiave con le chiavi esterne.

Domande frequenti

PostgreSQL ha AUTO_INCREMENT?

Non con quel nome. Il modo standard è una colonna identity, id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, disponibile da PostgreSQL 10. I vecchi tipi serial e bigserial fanno lo stesso con una sequenza e un valore predefinito.

Perché la sequenza degli id in PostgreSQL ha dei buchi?

Una sequenza assegna un valore anche quando l'insert poi fallisce o la sua transazione viene annullata, e quel valore non viene mai riusato. I buchi sono normali e innocui, quindi non contare sul fatto che gli id siano consecutivi.

Una chiave primaria crea un indice in PostgreSQL?

Sì. Una chiave primaria (e ogni vincolo UNIQUE) crea in automatico un indice B-tree univoco, che per la chiave primaria si chiama tablename_pkey.

Meglio UUID o bigint per una chiave primaria?

Le chiavi identity bigint sono più piccole (8 byte contro 16) e si inseriscono nell'ordine dell'indice. Gli UUID si possono generare ovunque senza passare dal database e non rivelano quante righe esistono. Gli UUID casuali sparpagliano gli insert in tutto l'indice, e sulle tabelle grandi questo costa di più.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia