Come usare COALESCE in PostgreSQL

In questo post, capiremo cos’è la funzione COALESCE e come usarla in PostgreSQL.

Che cos’è una funzione COALESCE?

COALESCE è una funzione integrata nel sistema che può essere considerata una delle espressioni condizionali disponibili in PostgreSQL. NULLIF, GREATEST, LEAST e COALESCE sono i tipi di espressioni condizionali in PostgreSQL.

La funzione COALESCE restituisce la prima espressione non NULL nell’elenco specificato. Se tutti gli argomenti sono NULL, restituirà NULL come output.

supportati sintassi è:

COALESCE(value_1,value_2,value_3,........value_n)

I parametri o argomenti sono:

value_1,value_2,value_3,........value_n

Avvertenze

alcuni punti circa la funzione COALESCE:

• I parametri sono testati per valori non nulli.
• I parametri devono essere tutti dello stesso tipo di dati. Altrimenti si tradurrà in un errore.
• La funzione COALESCE accetta un numero illimitato di argomenti.
• Prende gli argomenti da sinistra a destra.
• Funzione Coalesce fornire compatibilità simile come NVL e IFNULL che sono disponibili in altri DBMS.
• CASE e COALESCE possono essere usati per scopi simili, ma il vantaggio di usare COALESCE è che richiede molto meno digitazione.
• Smetterà di valutare i parametri non appena viene raggiunto il primo valore non NULLO.

Esempi

1. Ecco come funziona COALESCE con un valore non NULL per il primo parametro:

postgres=# select coalesce (1,null,2); coalesce ---------- 1(1 row)

La funzione COALESCE trova la prima espressione non NULL all’inizio, quindi produrrà sempre lo stesso risultato indipendentemente da ciò che contengono i parametri rimanenti.

2. Ecco come funziona COALESCE quando un valore NULL è il primo parametro:

postgres=# select coalesce (null,1,2); coalesce ---------- 1(1 row)

La funzione COALESCE ha trovato un valore NULL nel primo parametro, quindi ha continuato al secondo parametro, che non era NULL, in modo che il valore del parametro sia stato restituito.

3. Ecco come interpreta più valori NULL:

postgres=# SELECT coalesce(null,null, 1, 2, 3, null, 4); coalesce ---------- 1(1 row)

Ci sono più NULL in questa espressione, ma poiché il primo valore non NULL è stato raggiunto sul terzo parametro, tutti i valori rimanenti sono stati ignorati.

4. Se i tipi di dati dei parametri non corrispondono, questo produrrà un errore:

postgres=# SELECT coalesce(1,now(),null);ERROR: COALESCE types integer and timestamp with time zone cannot be matched

Il primo parametro è un numero intero e la funzione now() restituisce un timestamp con fuso orario. Poiché questi parametri non sono dello stesso tipo, la firma della funzione non è valida e quindi produce e error.

5. È possibile utilizzare altre funzioni come parametri all’interno della funzione COALESCE:

postgres=# SELECT coalesce(nvl(null,199),least(8,null),avg(9.9)); coalesce ---------- 199(1 row)

Prevenzione dei valori NULL con COALESCE

Un’applicazione utile della funzione COALESCE è impedire che i valori NULL vengano visualizzati nei risultati delle query.

Esempio

1. Creare una tabella “vendite” utilizzando il comando CREA TABELLA:

postgres=# CREATE TABLE sales (serial_id int primary key, product_name varchar(50), amount numeric, discount numeric); CREATE TABLE

2. Inserire i dati nella tabella “vendite”:

postgres=# INSERT INTO sales VALUES (121,'paste',1000,10), (144,'brush',1500,20), (166,'soap',800,0), (178,'coffee',500,NULL);INSERT 0 4

3. Se proviamo a eseguire sottrarre un valore nella colonna ” sconto “da un valore nella colonna” importo”, l’ultima riga restituirà un valore NULLO. Questo perché qualsiasi aritmetica eseguita su un valore NULL risulterà in NULL.

postgres=# SELECT amount - discount as "final bill" FROM sales; final bill ------------ 990 1480 800 <=Null(4 rows)