Inleiding
Wanneer u gegevens in PostgreSQL beheert, zult u merken dat NULL waarden een beetje lastig te hanteren kunnen zijn. Bijvoorbeeld, de meeste expressies retourneren een waarde van NULL wanneer een element van de expressie NULL is. Hoe kunnen we enkele van de moeilijkheden vermijden die geassocieerd worden met NULL waarden? In het eerste artikel van deze tweedelige serie hebben we uitgelegd hoe de PostgreSQL COALESCE functie kan worden gebruikt om te testen op NULL waarden en een andere waarde in plaats van de NULLs te vervangen. Dit artikel gaat verder met de COALESCE functie en de toepassingen ervan in PostgreSQL.
Voorvereisten
Voordat u de voorbeelden in dit artikel gaat volgen, moet u er zeker van zijn dat PostgreSQL al op uw computer is geïnstalleerd. U zou ook enige inleidende kennis van PostgreSQL moeten hebben om de voorbeelden van de COALESCE functie te kunnen begrijpen.
COALESCE in psql command-line interface
In het vorige artikel hebben we uitgelegd hoe u toegang krijgt tot de psql command-line interface voor PostgreSQL. We moeten de psql command-line console nog een keer openen om enkele COALESCE voorbeelden uit te proberen.
Laten we beginnen met het eenvoudige voorbeeld hieronder:
|
1
|
SELECT COALESCE ( ‘apple’ , ‘pear’ , ‘strawberry’);
|
De uitvoer van deze query zal zijn:
|
1
2 3 |
COALESCE
———- apple |
Zoals u kunt zien, is de geretourneerde uitvoer ‘appel’, omdat dit de eerste niet-NULL waarde in de argumentenlijst is.
Als we een NULL-waarde als eerste item van de lijst hebben, zal de COALESCE-functie deze overslaan en op zoek gaan naar de eerste waarde die niet NULL is. We kunnen zien hoe dit werkt in de volgende query:
|
1
|
SELECT COALESCE ( NULL, ‘apple’ , ‘pear’ , ‘strawberry’);
|
De uitvoer zal zijn:
|
1
2 3 4 |
COALESCE
———- appels (1 ROW) |
Gebruik PostgreSQL COALESCE timestamp null
In ons volgende voorbeeld, zullen we proberen de COALESCE-functie te gebruiken met het gegevenstype timestamp.
Eerst moeten we een andere tabel in PostgreSQL maken:
|
1
2 3 4 5 6 7 |
CREATE TABLE books(
book_id INT PRIMARY KEY NOT NULL, book_name VARCHAR NOT NULL, quantity INT, price REAL, year_released TIMESTAMP ); |
We moeten ook records invoegen in de books tabel die we zojuist hebben gemaakt:
|
1
2 3 4 5 6 7 |
INSERT INTO books(book_name, quantity, price, year_released)
VALUES (‘Book1’, 12, 60, ‘2015-07-21 09:10:25+8’), (‘Boek2’, 5, 55, ‘2018-02-12 15:40:15+8’), (‘Boek3’, 10, 90, ‘2017-11-12 00:10:11+8’), (‘Boek4’, 26, 47, NULL), (‘Boek5’, 2, 83, ‘2019-03-05 03:05:08+8’); |
Merk op dat we een NULL-waarde in onze tabel hebben ingevoegd in de kolom year_released, die een gegevenstype van timestamp heeft.
Stellen we ons voor dat we de totale prijs van elk boek willen weten. Om die informatie te krijgen, vermenigvuldigen we de waarden van twee kolommen: quantity en price. De onderstaande query laat zien hoe dit wordt gedaan:
|
1
2 |
SELECT book_id, book_name, ( quantity * price ) AS total_price
FROM books; |
De uitvoer van de query zal er als volgt uitzien:
|
1
2 3 4 5 6 7 8 |
book_id | book_name | total_price
———+———–+————- 1 | Boek1 | 720 2 | Boek2 | 275 3 | Boek3 | 900 4 | Boek4 | 1222 5 | Boek5 | 166 (5 ROWS) |
Laten we eens kijken naar de inhoud van de tabel books met deze query:
|
1
|
SELECT * FROM books;
|
De uitvoer zou er als volgt uitzien:
|
1
2 3 4 5 6 7 8 |
book_id | book_name | quantity | price | year_released
———+———–+———-+——-+——————— 1 | Boek1 | 12 | 60 | 2015-07-21 09:10:25 2 | Book2 | 5 | 55 | 2018-02-12 15:40:15 3 | Book3 | 10 | 90 | 2017-11-12 00:10:11 4 | Book4 | 26 | 47 | 5 | Book5 | 2 | 83 | 2019-03-05 03:05:08 (5 rijen) |
- Merk op dat er een NULL-waarde staat in de laatste kolom van de vierde rij. We kunnen dit veranderen om een niet-NULL waarde te gebruiken met de functie
COALESCE:
|
1
2 |
SELECT book_id, book_name,
COALESCE(year_released, CURRENT_TIMESTAMP) FROM books; |
Hier ziet de uitvoer er als volgt uit:
|
1
2 3 4 5 6 7 8 |
boek_id | boek_naam | COALESCE
———+———–+——————————- 1 | Boek1 | 2015-07-21 09.:10:25-04 2 | Book2 | 2018-02-12 15:40:15-05 3 | Book3 | 2017-11-12 00:10:11-05 4 | Book4 | 2020-02-13 06:20:41.239926-05 5 | Book5 | 2019-03-05 03:05:08-05 (5 ROWS) |
Conclusie
In deze tweedelige artikelreeks hebben we de PostgreSQL COALESCE-functie grondig bekeken. We hebben verschillende voorbeelden getoond van hoe deze functie kan worden gebruikt om te testen op NULL waarden in uw gegevens en indien nodig een niet-NULL waarde te vervangen. Met deze voorbeelden als leidraad bent u voorbereid om de COALESCE functie in uw eigen PostgreSQL verklaringen op te nemen.