Creare e gestire le colonne generate

Mantieni tutto organizzato con le raccolte Salva e classifica i contenuti in base alle tue preferenze.

Una colonna generata è una colonna che viene sempre calcolata da altre colonne in una riga. Queste colonne possono semplificare una query, risparmiare sul costo della valutazione di un'espressione al momento della query e possono essere indicizzate o utilizzate come chiave esterna. In questo articolo illustreremo come gestire questo tipo di colonna nel database.

Aggiungere una colonna generata a una nuova tabella

Nello snippet CREATE TABLE che segue, viene creata una tabella per archiviare le informazioni sugli utenti. Abbiamo colonne per FirstName e LastName e definiamo una colonna generata per FullName, che è la concatenazione di FirstName e LastName

SQL standard di Google 

CREATE TABLE Users (
  Id STRING(20) NOT NULL,
  FirstName STRING(50),
  LastName STRING(50),
  Age INT64 NOT NULL,
  FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);

PostgreSQL 

CREATE TABLE users (
  id VARCHAR(20) NOT NULL,
  firstname VARCHAR(50),
  lastname VARCHAR(50),
  age BIGINT NOT NULL,
  fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
  PRIMARY KEY(id)
);

Il valore di FullName viene calcolato quando viene inserita una nuova riga o quando vengono aggiornati FirstName e/o LastName per una riga esistente. Il valore calcolato viene memorizzato insieme ad altre colonne della tabella. L'SQL tra parentesi è chiamato espressione di generazione.

  • expression può essere qualsiasi espressione SQL valida assegnabile al tipo di dati di colonna con le restrizioni seguenti.

  • L'attributo STORED che segue l'espressione determina la memorizzazione della funzione insieme ad altre colonne della tabella. I successivi aggiornamenti di una delle colonne a cui viene fatto riferimento fanno sì che l'espressione venga rivalutata e archiviata.

  • Non sono consentite colonne generate senza l'attributo STORED.

  • Non sono consentite scritture dirette nelle colonne generate.

  • Le colonne generate non possono essere utilizzate come o come parte principale di una chiave primaria. Tuttavia, possono essere chiavi indice secondarie.

  • L'opzione della colonna allow_commit_timestamp non è consentita nelle colonne generate o nelle colonne a cui si fa riferimento nelle colonne generate.

  • Non puoi modificare il tipo di dati di una colonna generata o qualsiasi colonna a cui si fa riferimento nella colonna generata.

  • Non puoi eliminare una colonna a cui si fa riferimento in una colonna generata.

Puoi eseguire query sulla colonna generata come qualsiasi altra colonna, come mostrato nell'esempio riportato di seguito.

SQL standard di Google 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

Equivale alla seguente istruzione, che non utilizza la colonna generata.

SQL standard di Google 

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL 

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

Oltre a semplificare la query e a risparmiare sui costi di valutazione dell'espressione al momento della query, una colonna generata può anche essere indicizzata o utilizzata come chiave esterna.

Creare un indice in una colonna generata

Per facilitare le ricerche nella colonna FullName generata, possiamo creare un indice secondario come mostrato nello snippet seguente.

SQL standard di Google 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Aggiungere una colonna generata a una tabella esistente

Utilizzando la seguente istruzione ALTER TABLE, aggiungiamo una colonna generata alla tabella Users per generare e archiviare le iniziali dell'utente.

SQL standard di Google 

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

PostgreSQL 

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(FirstName, 0, 1) || SUBSTR(LastName, 0, 1)) STORED;

L'aggiunta di una colonna generata memorizzata a una tabella esistente è un'operazione a lunga esecuzione per eseguire il backfill dei valori della colonna. Durante il backfill, non è possibile leggere o eseguire query sulle colonne generate archiviate. Lo stato di backfill si riflette in INFORMATION_SCHEMA.

Creare un indice parziale utilizzando una colonna generata

E se volessimo inviare query solo agli utenti che hanno più di 18 anni? Una scansione completa della tabella non sarebbe efficiente, pertanto utilizziamo un indice parziale.

  1. Utilizza la seguente istruzione per aggiungere un'altra colonna generata che restituisce l'età dell'utente se ha più di 18 anni e restituisce NULL altrimenti.

    SQL standard di Google 

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
AS (IF(Age > 18, Age, NULL)) STORED;

    PostgreSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) STORED;

  2. Crea un indice in questa nuova colonna e disattiva l'indicizzazione dei valori NULL con la parola chiave NULL_FILTERED in Google Standard SQL o il predicato IS NOT NULL in PostgreSQL. Questo indice parziale è più piccolo e più efficiente di un indice normale perché esclude tutti coloro che hanno meno di 18 anni.

    SQL standard di Google 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. Per recuperare Id e Age di tutti gli utenti maggiori di 18 anni, esegui la query seguente.

    SQL standard di Google 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 IS NOT NULL;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 IS NOT NULL;

  4. Per filtrare in base a un'età diversa, ad esempio per recuperare tutti gli utenti che hanno più di 21 anni, utilizza lo stesso indice e filtro nella colonna generata come segue:

    SQL standard di Google 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 > 21;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 > 21;

Rimuovere una colonna generata

La seguente istruzione DDL rilascia una colonna generata dalla tabella Users.

ALTER TABLE Users
DROP COLUMN Initials;

Modificare un'espressione di colonna generata

Non è consentito modificare l'espressione di una colonna generata. Dovrai invece eliminare la colonna esistente e crearne una nuova con la nuova espressione.

Visualizzare le proprietà di una colonna generata

INFORMATION_SCHEMA di Spanner contiene informazioni sulle colonne generate nel tuo database. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere eseguendo una query sullo schema di informazioni.

Quali colonne generate sono definite nel mio database?

SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

Qual è lo stato corrente delle colonne generate nella tabella Users?

Se hai aggiunto una colonna generata a una tabella esistente, ti consigliamo di visualizzarne lo stato attuale per determinare, ad esempio, se la colonna è stata sottoposta a backfill e completamente utilizzabile. Se SPANNER_STATE restituisce WRITE_ONLY nella query seguente, significa che Spanner è ancora in fase di backfill della colonna e non sono consentite letture.

SQL standard di Google 

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;

Passaggi successivi