Postgres SQL Nuance
I ran across an interesting nuance between Oracle and Postgres with the double-pipe operator. I found that the following query failed to cross port from Oracle to Postgres:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | COL account_number FORMAT A10 HEADING "Account|Number" COL full_name FORMAT A16 HEADING "Name|(Last, First MI)" COL city FORMAT A12 HEADING "City" COL state_province FORMAT A10 HEADING "State" COL telephone FORMAT A18 HEADING "Telephone" SELECT m.account_number , c.last_name || ', ' || c.first_name || CASE WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name END AS full_name , a.city , a.state_province , t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone FROM member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN address a ON c.contact_id = a.contact_id INNER JOIN street_address sa ON a.address_id = sa.address_id INNER JOIN telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id WHERE c.last_name = 'Winn'; |
In Oracle, a CASE statement ignores the null of a missing ELSE clause between lines 4 and 5. Oracle assumes a null value is an empty string when concatenated to a string with the double-piped concatenation operator. Oracle’s implementation differs from the ANSI standard and is non-compliant.
It would display the following thanks to the SQL reporting features that don’t exist in other Command-Line Interface (CLI) implementations, like mysql, psql, sqlcmd, or cql:
Account Name Number (Last, First MI) City State Telephone ---------- ---------------- ------------ ---------- ------------------ B293-71445 Winn, Randi San Jose CA 001-(408) 111-1111 B293-71445 Winn, Brian San Jose CA 001-(408) 111-1111 |
However, it fails in Postgres without a notice, warning, or error. Postgres simply returns a null string for the missing ELSE clause and follows the rule that any string concatenated against a null is a null. That means it retunes a null value for the full_name column above. The Postgres behavior is the ANSI standard behavior. After years of working with Oracle it was interesting to have this pointed out while porting a query.
You can fix the statement in Postgres by adding an explicit ELSE clause on a new line 5 that appends an empty string, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT m.account_number , c.last_name || ', ' || c.first_name || CASE WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name ELSE '' END AS full_name , a.city , a.state_province , t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone FROM member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN address a ON c.contact_id = a.contact_id INNER JOIN street_address sa ON a.address_id = sa.address_id INNER JOIN telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id WHERE c.last_name = 'Winn'; |
It would display:
account_number | full_name | city | state_province | telephone ----------------+-------------+----------+----------------+-------------------- B293-71445 | Winn, Randi | San Jose | CA | 001-(408) 111-1111 B293-71445 | Winn, Brian | San Jose | CA | 001-(408) 111-1111 (2 rows) |
As always, I hope this helps those looking to solve a problem.
Subscribe via RSS