Capire le viste Information_Schema di PostgreSQL

In questo articolo approfondiamo uno dei sistemi integrati di PostgreSQL, noto come information_schema.

Prima di continuare a leggere, volevo parlarti del nostro gestore di database PostgreSQL moderno e veloce, Beekeeper Studio. Mostra gli stessi metadati, da tabelle e colonne fino a indici e chiavi esterne, nella barra laterale e nella vista struttura, ed è gratuito per PostgreSQL: scaricalo qui.

Cos'è Information_Schema?

Information_schema è un catalogo di sistema che costituisce un insieme standard di viste contenenti informazioni sugli oggetti del database PostgreSQL: in sostanza, è un meta-database che conserva informazioni sul tuo database corrente. È presente in tutti i motori di database SQL conformi agli standard, il che lo rende molto utile durante le migrazioni.

Va notato che information_schema è di sola lettura e non consente l'inserimento di nuove informazioni né la modifica dei dati esistenti. Iniziamo a esplorare le diverse viste di information_schema che possono esserti utili in vari casi.

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'information_schema';

Il codice è compatibile con tutte le versioni di PostgreSQL.
Una volta eseguito, questo codice restituisce l'elenco di tutte le viste disponibili per gli utenti in information_schema.

Perché usare Information_Schema?

Information_schema è fondamentale quando vuoi lavorare in modo più efficace con le strutture del database, in particolare per quanto riguarda:

• Tabelle
• Colonne
• Indici
• Trigger
• Vincoli
• Schemi

Information_Schema.Tables

La vista information_schema.tables contiene i dettagli di tutte le tabelle del database a cui sei connesso.

SELECT table_name 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' 
AND table_schema NOT IN ('pg_catalog', 'information_schema');

Il codice è compatibile con tutte le versioni di PostgreSQL. Il risultato sarà l'elenco di tutte le tabelle di base del tuo database.

Information_Schema.Columns

La vista information_schema.columns ti permette di recuperare i dettagli sulle colonne del tuo database.

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name   = 'your_table';

Il codice è compatibile con qualsiasi versione di PostgreSQL. Sostituisci ‘your_table’ con il nome della tua tabella per ottenere l'elenco di tutte le colonne, con i relativi tipi di dato, della tabella indicata.

Information_Schema.Triggers

Le informazioni sui trigger possono essere utili durante il debug o per capire le dipendenze. La vista information_schema.triggers serve a recuperare i dettagli sui trigger collegati al database.

SELECT event_object_table, trigger_name, action_statement, action_timing 
FROM information_schema.triggers;

Questo codice, compatibile con tutte le versioni di PostgreSQL, restituisce il nome della tabella, il nome del trigger, l'azione del trigger e il suo momento di esecuzione.

Information_Schema.Key_Column_Usage

La vista information_schema.key_column_usage fornisce informazioni che rendono più facile capire le relazioni di chiave esterna nel tuo database.

SELECT constraint_name, table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 'your_table';

Sostituisci ‘your_table’ con il nome della tua tabella ed esegui il codice, compatibile con tutte le versioni di PostgreSQL, per ottenere constraint_name, table_name, column_name e ordinal_position di ogni chiave della tabella indicata.

Conclusione

In questo tutorial abbiamo esplorato nel dettaglio information_schema di PostgreSQL. Sfruttando le sue viste puoi ottenere un controllo maggiore e una visione più chiara della struttura e degli elementi del tuo database. Se stai passando a PostgreSQL da un altro database SQL, troverai le viste di information_schema particolarmente comode, dato che fanno parte dello standard SQL.

La tua avventura con PostgreSQL non finisce qui. Resta sintonizzato per le prossime guide, in cui approfondiremo il vasto universo di PostgreSQL con argomenti più avanzati, consigli e trucchi.