Questo articolo mostra la procedura da seguire per creare una tabella esterna contenente dati ricavati da un file CSV locale e sfrutta un’altra caratteristica introdotta in PostgreSQL 9.1: le estensioni.

Prima di tutto, è necessario installare una estensione chiamata file_fdw distribuita nei moduli contrib di PostgreSQL 9.1.

Per installare i moduli contrib di PostgreSQL 9.1 dai sorgenti è sufficiente compilarli con gmake world e installarli con gmake install-world, oppure entrare nella directory contrib degli stessi sorgenti e digitare gmake install.

Una volta installati i moduli contrib, le estensioni si troveranno in ${postgresql_prefix}/share/extension. Controllate che esistano i file file_fdw.control e file_fdw--1.0.sql, che definiscono l’estensione (si rimanda all’articolo sulle estensioni per maggiori informazioni).

Creare l’estensione con:

CREATE EXTENSION file_fdw;

Il comando psql per mostrare le estensioni è dx, che possiede anche la variante dx+, che mostra maggiori dettagli:

test_db=# dx+ file_fdw
Objects in extension "file_fdw"
Object Description
-----------------------------------------
foreign-data wrapper file_fdw
function file_fdw_handler()
function file_fdw_validator(text[],oid)
(3 rows)

Si noti che questa estensione crea automaticamente un oggetto di tipo FOREIGN DATA WRAPPER di nome file_fdw, che tornerà utile in seguito.

Dato che SQL/MED può gestire la copia dei dati anche attraverso database remoti, è necessario creare un server usando l’oggetto file_fdw (questo passo è necessario anche nel caso si stiano caricando dati da un file locale, come in questo esempio):

CREATE SERVER file FOREIGN DATA WRAPPER file_fdw ;

A questo punto, è possibile creare la tabella esterna caricando i dati, con:

CREATE FOREIGN TABLE statistical_data (field1 numeric, field2 numeric)
SERVER file
OPTIONS (filename '/tmp/statistical_data.csv', format 'csv', delimiter ';') ;

Attualmente, è possibile eseguire solo query di sola lettura tramite SELECT sulle tabelle foreign. Le tabelle foreign inoltre, funzionano anche attraverso dblink, il tool che permette la comunicazione tra database remoti.

Concludendo, questa caratteristica va a completare una piccola lacuna che Postgres aveva rispetto ad altri database. Va pertanto accolta con grande interesse data la sua utilità, specialmente nel settore del data warehousing, in particolare dell’estrazione e del caricamento dati (ETL), e in più in generale per l’integrazione del database con altri sistemi e altre fonti di dati.

Per maggiori informazioni si rimanda alla documentazione di PostgreSQL sul comando CREATE FOREIGN TABLE.

Eliminando del tutto la scrittura dei file WAL e di conseguenza l’operazione di fsync() al momento del COMMIT della transazione, aumenta considerevolmente la velocità di scrittura su queste particolari tabelle:

postgres=# CREATE TABLE tabella-normale AS SELECT generate_series( 1,1000000 );
SELECT 1000000
Time: 7626.138 ms
postgres=# CREATE UNLOGGED TABLE tabella-unlogged AS SELECT generate_series( 1,1000000 );
SELECT 1000000
Time: 503.786 ms

D’altra parte, l’assenza di file WAL relativi alle operazioni sulla tabella fa sì che il contenuto delle tabelle unlogged non venga mantenuto dopo un riavvio del server causato da un crash (potete divertirvi a provare questo comportamento usando il comando kill sul processo server – preferibilmente non in produzione).

Inoltre, se si usa un sistema di replica come warm standby o hot standby, il contenuto delle tabelle unlogged non viene replicato sui vari slave.

Concludendo, questa nuova funzionalità introduce vantaggi per la memorizzazione di dati facilmente generabili e non durabili. Un caso d’uso comune potrebbe essere il caching, dove la velocità di scrittura è fondamentale e allo stesso tempo non ci si deve preoccupare della persistenza dei dati dopo un crash del server.

Per maggiori informazioni: http://www.postgresql.org/docs/9.1/static/sql-createtable.html.

ll presente articolo è ispirato dal Wiki sulle novità della release 9.1 redatto dalla comunità PostgreSQL.

L’ereditarietà in PostgreSQL consente di creare relazioni di tipo IS molto simile ai concetti tipici dell’OO riguardanti le classi. La tecnica è particolarmente usata per il partizionamento.

Una volta collegati a PostgreSQL, è sufficiente digitare la seguente query SQL:

SELECT DISTINCT relname FROM pg_inherits JOIN pg_class ON (oid = inhparent);

Espandendo la query, possiamo anche contare quante tabelle derivano dalla master, ordinandole per nome:

SELECT relname, count(*) FROM pg_inherits JOIN pg_class ON (oid = inhparent) GROUP BY 1 ORDER BY 1;

Buon divertimento!

Il primo passo consiste nell’abilitare i backport:

cat << E_O_BACKPORTS > /etc/apt/sources.list.d/backports.list deb http://www.backports.org/debian lenny-backports main contrib non-free deb-src http://www.backports.org/debian lenny-backports main contrib non-free E_O_BACKPORTS

cat << E_O_APTPREF >> /etc/apt/preferences

Package: 2ndquadrant_italia_mod.txt 2ndquadrant_italia.txt da_installare_pandoc hdoisajds.sh risultati step2 Pin: release a=lenny-backports Pin-Priority: 200 E_O_APTPREF

wget -O – http://backports.org/debian/archive.key | apt-key add –

apt-get update

Poi installare postgresql-8.4

apt-get -t lenny-backports postgresql-8.4 postgresql-contrib-8.4 postgresql-doc-8.4

Semplice, no?

Dopo avere trattato le novità in materia di SQL, di amministrazione e di stored procedure, cercherò di elencare alcune fra le restanti novità di PostgreSQL 8.4. In particolare:sicurezza, performance e strumenti a disposizione per gli utenti (come psql).

Prestazioni

Come ogni evoluzione precedente, anche Postgres 8.4 è stato oggetto di cambiamenti volti a migliorarne le prestazioni e le performance. Tra le modifiche principali, spiccano:

1. utilizzo di metodi basati hash per interrogazioni di tipo SELECT DISTINCT, UNION/INTERSECT/EXCEPTION (in precedenza, per queste operazioni Postgres era costretto a ordinare i dati e quindi ad eliminarli per ottenere valori distinti);
2. il valore di default del parametro default_statistics_target per il planner è stato aumentato a 100, e il valore massimo da 1000 a 10000 (utile per data warehouse)
3. ottimizzazione del planner per query EXISTS/NOT EXISTS e sub-select;
4. migliorate le prestazioni per operazioni di caricamento di massa (bulk load).

Permessi a livello di colonna

Al fine di proteggere dati sensibili e di garantire un maggiore controllo sull’accesso agli stessi da parte degli utilizzatori di database, gli amministratori sono finalmente in grado di concedere o revocare permessi di lettura, scrittura e aggiornamento su di un singolo campo di una tabella.

Maggiori approfondimenti:

• Il comando GRANT
• Il comando REVOKE

Autenticazione SSL

Gli utenti possono essere ora autenticati utilizzando certificati SSL. Gli amministratori possono definire politiche di accesso basate su specifici certificati SSL. Inoltre, PostgreSQL 8.4 supporta le catene di certificati SSL.

Ripristino in parallelo (parallel restore)

pg_restore supporta la modalità di processing parallelo, permettendo il caricamento dei dati e la creazione degli oggetti all’interno del database in diversi flussi paralleli. A seconda dell’hardware a disposizione e della struttura del database, questa funzionalità permette ridurre la durata del ripristino di file di backup fino a 8 volte rispetto al restore tradizionale su singolo processo. Si noti che il restore parallelo di PostgreSQL 8.4 può essere utilizzato anche per operazioni di ripristino di database su Postgres 8.3 e 8.2.

È possibile abilitare il processing parallelo specificando il numero di job da riga di comando, con l’opzione -j o --jobs.

Documentazione per l’applicazione pg_restore.

Miglioramenti a psql (applicazione client da console)

L’applicazione psql, la più utilizzata dagli amministratori di database PostgreSQL, è stata oggetto di notevoli migliorie. Di seguito è fornita una lista delle principali modifiche:

1. migliorata la gestione delle linee di comando e dei caratteri di tabulazione
2. aggiunte informazioni sul tipo di memorizzazione su disco delle colonne (i.e. plain, extended, ecc.)
3. migliorata la visualizzazione delle sequenze
4. migliorati la gestione e il controllo dell’opzione timing
5. aggiunta la visualizzazione dei valori accettati per i tipi di dato enum
6. aggiunta la visualizzazione della dimensione di una tabella (escluse tabelle collegate e indici), con il comando dt+
7. il comando d NOME_TABELLA permette di visualizzare le chiavi esterne collegate a campi della tabella corrente (molto utile e comodo per vedere i vincoli di integrità referenziale direttamente dalla tabella master)
8. aggiunta la visualizzazione della dimensione del database e dei relativi tablespace, rispettivamente con i comandi l e l+
9. migliorata la funzionalità di auto-completamento per tabelle su schemi multipli
10. rimossi gli oggetti di sistema dalla visualizzazione nei comandi della famiglia d: ad esempio df mostrerà soltanto le funzioni definite dall’utente e non più anche quelle di sistema (finalmente!), visualizzabili con il comando dfS
11. aggiunta la possibilità di editare direttamente da psql le funzioni utilizzando l’editor preferito con il comando ef NOME_FUNZIONE

Finalmente, siamo giunti alla fine di questo speciale. Spero di avere reso giustizia agli sviluppatori di PostgreSQL e alle novità da loro introdotte (circa 300!) in questo anno e mezzo di sviluppo della versione 8.4. E soprattutto spero che questi articoli in italiano possano avvicinare nuovi utenti a PostgreSQL.

A questo punto, non ci resta che aspettare l’uscita di PostgreSQL 8.4, attesa per la fine di giugno. Ciao!

Questo articolo è una traduzione da me riadattata del documento "What’s new in 8.4" del PostgreSQL Global Development Group. Ringrazio inoltre Hubert Lubaczewski per la serie di articoli "Waiting for 8.4".

Funzioni con un numero variabile di parametri ( xml:lang=”en”>variadic parameter)

Le funzioni variadic, ovvero che accettano un numero variabile di parametri, sono adesso parte integrante di PostgreSQL grazie all’introduzione della parola chiave VARIADIC in fase di specifica del parametro di input della procedura/funzione.

Per approfondimenti:

• hreflang=”en”>Le funzioni SQL con un numero variabile di argomenti
• hreflang=”en” xml:lang=”en”>Waiting for 8.4 – variadic functions

Parametri con valori di default

È stata aggiunta la possibilità di aggiungere parametri con valori di default nelle stored procedure, in modo da gestire i casi in cui l’utente di una particolare funzione non fornisca uno o più argomenti alla chiamata. Questa funzionalità rende molto più semplice la manutenzione delle stored procedure e la migrazione delle stesse da applicazioni di database su sistemi come SQL server e Sybase.

Per approfondimenti:

• hreflang=”en”>Le funzioni SQL con valori di default per gli argomenti
• hreflang=”en” xml:lang=”en”>Waiting for 8.4 – Default values for function arguments + integer in any base

Funzioni PL/pgSQL che ritornano una tabella (RETURNS TABLE)

Una scorciatoia conforme allo standard SQL per rendere più leggibile la specifica di funzioni con numerosi parametri in uscita, tramite la parola chiave RETURNS TABLE. La funzionalità è essenzialmente un alias di RETURNS SETOF, ma rende molto più veloce la scrittura di funzioni che si comportano come tabelle e che ritornano insiemi di righe.

Per approfondimenti:

• hreflang=”en”>Le funzioni SQL che ritornano una tabella
• hreflang=”en” xml:lang=”en”>Waiting for 8.4 – RETURNS TABLE

Struttura di controllo CASE in PL/pgSQL

Grazie all’introduzione dell’istruzione di controllo xml:lang=”en”>switch CASE in PL/pgSQL, che permette di eseguire codice basato sul confronto di un valore con una lista di condizione, vedremo progressivamente sparire l’utilizzo di blocchi IF .. ELSIF .. ELSIF .. ELSIF.

Per approfondimenti:

• hreflang=”en”>Le strutture di controllo in PL/pgSQL
• hreflang=”en” xml:lang=”en”>Waiting for 8.4 – CASE in PL/pgSQL

Potenziato il comando RAISE in PL/pgSQL per il lancio di eccezioni

Gli sviluppatori di stored procedure in PL/pgSQL ameranno molto le modifiche fatte da Pavel Stehule alla gestione delle eccezioni. Il comando RAISE è stato potenziato:

1. è in grado di collegare all’eccezione sollevata dall’utente e ai messaggi di errore informazioni di dettaglio (opzione DETAIL) e suggerimenti (opzione HINT)
2. è finalmente in grado di associare un codice di errore SQLSTATE all’eccezione (utilizzando sia codici predefiniti che non)
3. permette di associare nomi alle eccezioni utente
4. è possibile ri-sollevare una eccezione al blocco try esterno tramite il comando RAISE senza parametri ( in gergo tecnico rethrow)

Senza dubbio gli sviluppatori di stored procedure in PL/pgSQL hanno adesso a disposizione un set di funzionalità in grado di dar loro maggior controllo alla gestione delle eccezioni e degli errori.

Per approfondimenti:

• hreflang=”en”>Errori e messaggi in PL/pgSQL
• hreflang=”en” xml:lang=”en”>Waiting for 8.4 – PL/pgSQL RAISE

Supporto per parametri con EXECUTE in PL/pgSQL

La creazione di query dinamiche con EXECUTE è stata semplificata notevolmente con l’introduzione dei parametri di esecuzione, da specificare con la clausola USING. Con questa modifica, non è più necessario creare query dinamiche concatenando stringhe e valori.

Per approfondimenti:

• hreflang=”en”>l’istruzione EXECUTE in PL/pgSQL
• hreflang=”en” xml:lang=”en”>Waiting for 8.4 – PL/pgSQL EXECUTE ... USING

Supporto per RETURN QUERY EXECUTE in PL/pgSQL

È stata aggiunta la possibilità in PL/pgSQL di far ritornare alle funzioni il risultato di query dinamiche, tramite l’istruzione RETURN QUERY EXECUTE.

Per approfondimenti:

• hreflang=”en”>Strutture di controllo in PL/pgSQL
• hreflang=”en” xml:lang=”en”>Waiting for 8.4 – PL/pgSQL RETURN QUERY EXECUTE

Le funzioni che ritornano insiemi di record (SRF) possono essere richiamati all’interno di clausole SELECT

Questa funzionalità, finora utilizzabile soltanto nelle procedure in linguaggio SQL, è stata estesa ai linguaggi procedurali. Adesso è possibile eseguire query del tipo select i, funzione_test(1, i) from generate_series(1,3) i, dove funzione_test è una SRF scritta in un linguaggio procedurale come PL/pgSQL.

Approfondimento: Waiting for 8.4 – pl/* srf functions in selects

Trigger a livello di istruzione da scatenare in seguito a comando TRUNCATE

Ecco una funzionalità molto importante (soprattutto per i sistemi di replica basati su trigger) sviluppata dal nostro Simon Riggs che vede aggiungere la possibilità di specificare trigger a livello di istruzione (statement level trigger) da eseguire in seguito al verificarsi di un evento TRUNCATE su una tabella. Questa aggiunta è da considerarsi a tutti gli effetti una estensione di PostgreSQL, non contemplata dallo standard SQL.

Per approfondimenti (in inglese):

• Il comando CREATE TRIGGER
• Il comando TRUNCATE
• Panoramica sul comportamento dei trigger

Questo articolo è una traduzione da me riadattata del documento “What’s new in 8.4” del PostgreSQL Global Development Group. Ringrazio inoltre Hubert Lubaczewski per la serie di articoli “Waiting for 8.4“.

Ecco una lista delle principali novità in ambito "amministrazione" di PostgreSQL 8.4.

Collation a livello di database

Il tipo dell’ordine alfabetico delle stringhe di caratteri è da adesso una proprietà dei database invece che una proprietà del server definita a livello di inizializzazione. Questo permette agli utenti di avere molteplici linguaggi naturali (lingue) pienamente supportati dalla stessa installazione di PostgreSQL.

Questa funzionalità spiana la strada alle collation a livello di tabella e di colonna, presumibilmente già dalle prossime release di PostgreSQL.

Approfondimento sul comando CREATE DATABASE.

Mappa di visibilità (Visibility Map)

Grazie ad un registro in memoria che tiene traccia delle pagine "sporcate" dalle transazioni (la cosiddetta "mappa di visibilità"), il processo di VACUUM riesce a leggere solamente le pagine che necessitano di pulizia invece di scandire tutte le pagine della tabella. La mappa di visibilità permette pertanto di ridurre notevolmente i tempi di vacuum per tabelle di grandi dimensioni.

Approfondimento su "Visibility Map".

Mappa di spazio libero ottimizzata in modo automatico (free space map)

Una modifica molto importante al backend di PostgreSQL 8.4 è la gestione automatica della mappa di spazio libero (free space map). Fino alla versione 8.3 compresa, gli amministratori dovevano impostare il parametro max_fsm_pages per stabilire il numero di pagine su disco delle quali tenere traccia nella mappa di spazio libero all’interno della memoria condivisa. Questo parametro richiedeva un attento studio da parte degli amministratori del server di PostgreSQL.

Ebbene, con PostgreSQL 8.4, l’opzione max_fsm_pages è definitivamente sparita: il sistema è in grado di tenere traccia in modo automatico e con molta più precisione lo spazio libero all’interno di ogni pagina. Ad ogni tabella infatti viene affiancata una mappa di spazio libero memorizzata su disco.

Configurazione di auto-vacuum a livello di tabella con i comandi CREATE TABLE e ALTER TABLE / SET

Rispetto a PostgreSQL 8.3, non è più necessario inserire manualmente nel catalogo pg_autovacuum le configurazioni a livello di tabella per auto-vacuum. Adesso è possibile gestire la configurazione a livello di tabella con i comandi CREATE TABLE e ALTER TABLE. Inoltre (e finalmente), la configurazione di auto-vacuum è adesso salvata da pg_dump.

Per maggiori informazioni:

• il comando CREATE TABLE
• il comando ALTER TABLE

Esecuzione a tempo per pgbench

E’ possibile richiedere a pgbench "quante cose riesce a fare in uno specifico lasso di tempo", piuttosto che "quanto tempo impiega a eseguire uno specifico set di operazioni". La differenza è soprattutto nell’organizzazione dei test e nell’analisi dei risultati: paragonare il numero di query eseguito in un intervallo di tempo è molto più semplice ed efficace.

La funzione pg_conf_load_time()

Non dovrai più indovinare se il file postgresql.conf è più nuovo di quello utilizzato dal server PostgreSQL in azione: è possibile confrontare la sua data di modifica con quella del risultato della funzione pg_conf_load_time().

Visualizzazione fino a livello di colonna per EXPLAIN verbose

Adesso il comando EXPLAIN VERBOSE fornisce informazioni utili sulle colonne che ogni nodo dell’albero delle query restituisce al padre: questo tipo di informazioni mostra quanto pessima sia "SELECT *".

Riporta tutte le query coinvolte in un errore di deadlock

Informazioni sui deadlock sono fornite direttamente dal server PostgreSQL, senza necessità di scoprirle dai log.

Migliorate informazioni di pg_settings

pg_settings è in grado di mostrare le opzioni disponibili per le istruzioni globali di configurazione utente (GUC) con un insieme definito di valori. Molte impostazioni infatti accettano valori enumerati, e pg_settings è adesso in grado di mostrarli, facilitando la vita all’utente.

Migliorata la gestione dei certificati SSL

Le connessioni SSL possono adesso prevenire da attacchi del tipo man-in-the-middle, grazie alla verifica dei certificati del client.

Indici GIN multi-colonna

E’ possibile adesso avere indici GIN multi-colonna.

Approfondimento su indici GIN.

Miglioramenti al file pg_hba.conf

Adesso è possibile specificare opzioni di autenticazione nella forma NOME=VALORE. E’ stata inoltre rimossa l’opzione ident sameuser, rendendola di default nel caso in cui la usermap non sia specificata.

Per maggiori informazioni e per gli altri cambiamenti, si rimanda alla sezione della documentazione sul file pg_hba.conf.

Questo articolo è una traduzione da me riadattata del documento "What’s new in 8.4" del PostgreSQL Global Development Group. Ringrazio inoltre Hubert Lubaczewski per la serie di articoli "Waiting for 8.4".

Ecco una lista delle principali novità in ambito SQL di PostgreSQL 8.4.

Funzioni finestra (Windowing Functions)

Anche conosciute con il termine "windowing aggregate", le funzioni finestra permettono di effettuare operazioni di aggregazione (come count(), sum(), ecc.) e di rango come (rank() e row_number()) su un sottoinsieme dei dati (la cosiddetta finestra o window).

A livello pratico, questo comporta che report multi-livello che in precedenza avrebbero richiesto 3 o 4 query (e possibilmente la scrittura di procedure), possano essere ora generati con una singola query.

Le funzioni finestra ampliano il numero di applicazioni di Business Intelligence e supporto alle decisioni che PostgreSQL è in grado di supportare.

Approfondimento su Window Function e sulla sintassi delle chiamate.

Common Table Expression (CTE) e query ricorsive

Le Common Table Expression, anche conosciute con il nome di query WITH, permettono la creazione di subquery e di assegnar loro un nome. Le subquery possono a loro volta essere referenziate all’interno delle clausole della query alla quale appartengono.

Oltre a rimuovere la necessità di creare tabelle temporanee per alcune operazioni, le Common Table Expression consentono di eseguire query ricorsive nelle quali poter attraversare strutture ad albero o grafi all’interno di una singola query, in modo efficiente. Ciò risulta particolarmente importante per tutte le applicazioni che hanno dati organizzati in strutture gerarchiche come forum, gestori di file e organigrammi.

Approfondimento su Common Table Expression

Comando TABLE

Come specificato dallo standard SQL, il comando TABLE nome_tabella esegue la stessa identica mansione del comando SELECT 2ndquadrant_italia_mod.txt 2ndquadrant_italia.txt da_installare_pandoc hdoisajds.sh risultati step2 FROM nome_tabella.

Il comando SELECT

ALTER SEQUENCE RESTART e TRUNCATE TABLE RESTART IDENTITY

Tramite le istruzioni ALTER SEQUENCE RESTART e TRUNCATE TABLE RESTART IDENTITY è adesso possibile azzerare in modo semplice le sequenze, ripristinando il valore iniziale. La prima agisce sull’oggetto sequenza, la seconda azzera la sequenze associate alle colonne della tabella che si intende svuotare.

Per approfondimenti:

• Il comando ALTER SEQUENCE

• Il comando TRUNCATE

Aggiunta di una colonna con ALTER VIEW

Permette di aggiungere colonne alla fine di una vista esistente, senza dover ricostruire le dipendenze della vista. Modifiche o rimozioni di colonne continuano a richiedere la ricostruzione delle dipendenze.

LIMIT (espressione o subquery)

Adesso è possibile limitare il numero di righe restituite da una interrogazione al database utilizzando una espressione oppure addirittura una subquery. In precedenza, LIMIT vincolava all’utilizzo di una costante numerica. Questa modifica rende più facile per una singola vista o stored procedure ad esempio di supportare in modo dinamico meccanismi di paginazione.

Esempio per il recupero del primo 10% dei record della tabella notizie: SELECT 2ndquadrant_italia_mod.txt 2ndquadrant_italia.txt da_installare_pandoc hdoisajds.sh risultati step2 FROM notizie ORDER BY orario DESC LIMIT (SELECT count(*) / 10 FROM notizie);

Approfondimento su clausola LIMIT

Parola chiave AS opzionale per alias di colonna

Questa funzionalità, che renderà il passaggio da MySQL a PostgreSQL meno doloroso, rende opzionale la specifica della parola chiave "AS" nell’assegnazione di alias di colonna all’interno delle query.

Esempio: SELECT tablename tabella FROM pg_tables;

Il comando SELECT

Migliorata la conformità rispetto alla standard SQL per la gestione degli intervalli temporali

La specifica degli intervalli temporali è stata potenziata e resa più conforme rispetto allo standard SQL. E’ stato inoltre aggiunto il supporto per la specifica di intervalli secondo lo standard ISO 8601.

Esempio di specifica di intervallo secondo lo standard ISO 8601: SELECT INTERVAL 'P2Y1M1DT4H20M7.5S';

Tipi di dato per date e orari

Questo articolo è una traduzione da me riadattata del documento "What’s new in 8.4" del PostgreSQL Global Development Group. Ringrazio inoltre Hubert Lubaczewski per la serie di articoli "Waiting for 8.4".

PostgreSQL è in grado di memorizzare le date a partire dall’anno 4713 A.C. fino al 5874897 D.C., utilizzando il tipo di dato denominato "date", conforme allo standard SQL92. Ogni campo di tipo date occupa lo spazio interno di 4 byte. Al fine di poter inserire nel database oppure recuperare dal database campi di tipo data, PostgreSQL definisce un formato di default da utilizzare, tramite il parametro datestyle all’interno di postgresql.conf.

Questo parametro è solitamente impostato nel formato standard ISO 8601, che prevede la specifica di date nel formato "AAAA-MM-GG".

Ecco un estratto dal file di configurazione:

datestyle = 'iso, dmy'

E’ possibile visualizzare il valore del parametro datestyle anche da riga di comando, aprendo una sessione psql:

gabriele=> show datestyle;
DateStyle
-----------
ISO, DMY
(1 riga)

Prima di andare avanti, creiamo uno schema ed una tabella per fare degli esempi:

gabriele=> create schema test_data;
CREATE SCHEMA
gabriele=> set search_path to test_data;
gabriele=> create table test_data ( giorno date);
CREATE TABLE

Proviamo ad inserire la data odierna nella tabella test_data, utilizzando la funzione predefinita CURRENT_DATE:

gabriele=> insert into test_data values (CURRENT_DATE);
INSERT 0 1
gabriele=> SELECT 2ndquadrant_italia_mod.txt 2ndquadrant_italia.txt da_installare_pandoc hdoisajds.sh risultati step2 FROM test_data ;
giorno
------------
2009-05-03
(1 riga)

La cosa veramente utile ed interessante è che il parametro ‘datestyle‘ può essere reimpostato a livello di sessione. Ad esempio, una applicazione che si connette a PostgreSQL può volere inserire o ricevere dati in un formato diverso dal formato ISO 8601. Ad esempio, è possibile voler "negoziare" i dati nel formato "GG-MM-AAAA". Come fare?

La cosa è molto semplice. E’ sufficiente impostare lo stile della data a "Postgres, European", oppure "Postgres, DMY" (in quanto DMY è un sinonimo di European).

gabriele=> set datestyle to 'Postgres, DMY';
SET
gabriele=> SELECT 2ndquadrant_italia_mod.txt 2ndquadrant_italia.txt da_installare_pandoc hdoisajds.sh risultati step2 FROM test_data ;
giorno
------------
03-05-2009
(1 riga)

Se invece del segno meno (‘-‘) come separatore, desideriamo avere la barra (‘/’), possiamo utilizzare il formato SQL in datestyle:

gabriele=> set datestyle to 'SQL, DMY';
SET
gabriele=> SELECT 2ndquadrant_italia_mod.txt 2ndquadrant_italia.txt da_installare_pandoc hdoisajds.sh risultati step2 FROM test_data ;
giorno
------------
03/05/2009
(1 riga)

Utilizzando opportunamente la variabile datestyle è pertanto possibile in molti casi evitare di utilizzare funzioni di formattazione runtime come to_char o to_date. Non è possibile utilizzare questa soluzione se intendiamo avere nella stessa query più campi data con più di un formato di visualizzazione.

Per quanto riguarda l’immissione di valori, è opportuno segnalare che Postgres ha un sistema di parsing molto flessibile basato su euristiche e in grado di identificare formati di dati diversi (si veda l’Appendice B referenziata in fondo). Le date in formato ‘ISO 8601’ sono sempre accettate, per quanto riguarda i formati Postgres e SQL è necessario seguire la sottodirettiva che specifica l’ordine dei campi (DMY oppure MDY – il formato americano).

Infine, alcuni link utili:

• Manuale di PostgreSQL, Tipi data e orario
• Manuale di PostgreSQL, parametro DateStyle
• Manuale di PostgreSQL, Appendice B: supporto date e orari

Le sequenze in PostgreSQL sono un’ottimo strumento per gestire in modo concorrente l’assegnazione di numeri sequenziali. Spesso sono usati per gestire i campi identificativi (id) delle tabelle.

In PostgreSQL le sequenze possono essere usate in modo esplicito (creando l’oggetto di tipo sequence e poi assegnando il valore di default di un campo) oppure in modo implicito, dichiarando un tipo di dato come serial.

Per maggiori informazioni, si vedano le pagine del manuale su:

• CREATE SEQUENCE
• Tipo di dato SERIAL

Coloro che provengono dal mondo MySQL noteranno la somiglianza con il qualificatore AUTO_INCREMENT.

Ad ogni modo, l’obiettivo dell’articolo è quello di fornire un modo semplice per aggiornare il valore corrente di una sequence sulla base dei valori presenti nella tabella.

L’operazione ha un suo scopo nelle operazioni di caricamento dei dati.

Supponiamo di avere una tabella customers con il campo id_customer associato alla sequenza customers_seq.

CREATE SEQUENCE customers_seq;
CREATE TABLE customers (
id_customer INTEGER NOT NULL
DEFAULT nextval('customers_seq') PRIMARY KEY,
...
);

Supponiamo di volerla esportare:

copy customers TO 'dati/customers.txt'

e quindi importare in un’altra istanza di PostgreSQL o in un alto database. Il comando da lanciare utilizzando l’applicazione da console psql è:

copy customers FROM 'dati/customers.txt'

si occupa semplicemente di importare i dati. Ma in seguito a questa operazione, la sequenza non sarà stata aggiornata e il primo inserimento automatico di un record nella tabella genererà un errore di violazione della chiave primaria.

E’ pertanto necessario aggiornare il valore corrente della sequenza prima di mettere in produzione il database, lanciando una semplice query di aggiornamento:

select setval('customers_seq', max(id_customer)) FROM customers;

Come ultimo riferimento, si consiglia una lettura della pagina sulle funzioni per le sequenze.