Guida all'Information_Schema di PostgreSQL per la gestione dei database

Introduzione

Se ti occupi di gestione di database, ad esempio creando tabelle, esaminando relazioni o generando report, conoscere l'information_schema di PostgreSQL può esserti molto utile. In questo tutorial vedremo le basi dell'information_schema di PostgreSQL con alcuni esempi di codice.

Questo tutorial è pensato principalmente per PostgreSQL, anche se alcuni concetti possono valere per altri database relazionali SQL.

Prima di continuare a leggere, volevo parlarti del nostro gestore di database PostgreSQL moderno e veloce, Beekeeper Studio. Esplorare gli schemi diventa un gioco da ragazzi ed è gratuito per PostgreSQL: scaricalo qui.

Cos'è l'Information_Schema?

L'information_schema è una funzionalità fondamentale dei database PostgreSQL che dà accesso ai metadati del ‘database corrente’. In pratica è una raccolta di viste che contengono informazioni sui vari oggetti del ‘database corrente’. Da tabelle a colonne fino ai vincoli, l'information_schema di PostgreSQL fornisce i dettagli dell'architettura di un database.

SELECT *
FROM information_schema.tables;

Eseguendo il comando qui sopra nell'editor SQL di Beekeeper Studio, ad esempio, otterrai l'elenco delle tabelle del tuo database corrente.

Approfondiamo

Visualizzare le tabelle pubbliche

Per elencare tutte le tabelle pubbliche disponibili nel tuo database corrente, ti basta eseguire:

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

Questo comando restituisce l'elenco di tutti i nomi delle tabelle del tuo ‘database corrente’ nello schema ‘public’.

Output con il database di esempio ‘chinook’

Esaminare le colonne

Usa la vista information_schema.columns per ottenere informazioni dettagliate sulle colonne di una tabella specifica:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name ='album';

Questo codice restituisce l'elenco di tutti i nomi delle colonne e dei relativi tipi di dato per la tabella selezionata.

Output con il database di esempio ‘chinook’

Visualizzare i vincoli

La vista information_schema.table_constraints fornisce informazioni sui vincoli definiti nella tabella.
Puoi usare la query seguente per recuperare i vincoli di una tabella specifica:

SELECT constraint_name, constraint_type 
FROM information_schema.table_constraints 
WHERE table_name='album';

Eseguendo il comando otterrai i nomi e i tipi dei vincoli di ‘your_table’, così capirai quali regole vengono applicate a questa tabella.

Output con il database di esempio ‘chinook’

Conclusione

L'information_schema di PostgreSQL è uno strumento importante per esplorare e comprendere la struttura e i dettagli del tuo database. Come usarlo dipende dalle tue esigenze specifiche e dalla configurazione del database. Ricordati di sostituire i nomi di tabelle e colonne di esempio con quelli del tuo database, così i comandi verranno eseguiti correttamente.

Nota: tutti gli esempi di questo tutorial sono pensati per PostgreSQL (versioni da 9.4 a 13.3) e potrebbero non essere compatibili con altri motori di database. Assicurati sempre di adattare le query al motore di database che usi. La natura binaria dell'information_schema di PostgreSQL può rendere l'esplorazione del database più semplice, efficace e molto più rapida.