Jak używać COALESCE w PostgreSQL

w tym poście zrozumiemy, czym jest funkcja COALESCE i jak jej używać w PostgreSQL.

co to jest funkcja COALESCE?

COALESCE jest wbudowaną w system funkcją, którą można uznać za jedno z wyrażeń warunkowych dostępnych w PostgreSQL. NULLIF, GREATEST, LEAST I COALESCE to typy wyrażeń warunkowych w PostgreSQL.

funkcja COALESCE zwraca pierwsze wyrażenie inne niż NULL na podanej liście. Jeśli wszystkie argumenty są NULL, to zwróci NULL jako wynik.

wspierana składnia to:

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

parametry lub argumenty to:

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

zastrzeżenia

kilka punktów o funkcji COALESCE:

• parametry są testowane pod kątem wartości innych niż NULL.
• wszystkie parametry powinny być tego samego typu danych. W przeciwnym razie spowoduje to błąd.
• funkcja COALESCE przyjmuje nieograniczoną liczbę argumentów.
• pobiera argumenty od lewej do prawej.
• funkcja Coalesce zapewnia podobną kompatybilność jak NVL i IFNULL, które są dostępne w innych DBMS.
• CASE I COALESCE mogą być używane do podobnych celów, ale zaletą użycia COALESCE jest to, że wymaga znacznie mniej pisania.
• przestanie obliczać parametry, gdy tylko osiągnie pierwszą wartość inną niż NULL.

przykłady

1. Oto jak działa COALESCE z wartością inną niż NULL dla pierwszego parametru:

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

funkcja COALESCE znajduje pierwsze wyrażenie inne niż NULL na początku, więc zawsze da ten sam wynik niezależnie od tego, co zawierają pozostałe parametry.

2. Oto jak działa COALESCE, gdy wartość NULL jest pierwszym parametrem:

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

funkcja COALESCE znalazła wartość NULL w pierwszym parametrze, więc kontynuowała do drugiego parametru, który nie był NULL, więc wartość parametru została zwrócona.

3. Oto jak interpretuje wiele wartości NULL:

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

w tym wyrażeniu jest wiele null, ale ponieważ pierwsza wartość nie jest NULL została osiągnięta w trzecim parametrze, wszystkie pozostałe wartości zostały pominięte.

4. Jeśli typy danych parametrów nie pasują, spowoduje to błąd:

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

pierwszym parametrem jest liczba całkowita, a funkcja now() zwraca znacznik czasu ze strefą czasową. Ponieważ te parametry NIE są tego samego typu, podpis funkcji jest nieprawidłowy, a więc produkuje i błąd.

5. Możesz użyć innych funkcji jako parametrów wewnątrz funkcji COALESCE:

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

zapobieganie wartościom NULL za pomocą COALESCE

pomocnym zastosowaniem funkcji COALESCE jest zapobieganie pojawianiu się wartości NULL w wynikach zapytania.

przykład

1. Utwórz tabelę „sprzedaż”za pomocą polecenia Utwórz tabelę:

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

2. Wstaw dane do tabeli „sprzedaż”:

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. Jeśli spróbujemy odjąć wartość w kolumnie „rabat” od wartości w kolumnie „kwota”, ostatni wiersz zwróci wartość NULL. Dzieje się tak, ponieważ każda arytmetyka wykonywana na wartości NULL spowoduje NULL.

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