Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Friday, 11 October 2019 19:20

Postgres SQL Nuance

Written by  http://blog.mclaughlinsoftware.com/2019/10/12/postgres-sql-nuance/
Rate this item
(0 votes)

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:

123456789101112131415161718
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||CASEWHEN c.middle_name ISNOTNULLTHEN' '|| c.middle_name ENDAS full_name, a.city, a.state_province, t.country_code ||'-('|| t.area_code ||') '|| t.telephone_number AS telephoneFROM 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_idWHERE c.last_name ='Winn';

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 NameNumber (Last, First MI) City State Telephone---------- ---------------- ------------ ---------- ------------------B293-71445 Winn, Randi San Jose CA 001-(408) 111-1111B293-71445 Winn, Brian San Jose CA 001-(408) 111-1111

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:

1234567891011121314
SELECT m.account_number, c.last_name ||', '|| c.first_name||CASEWHEN c.middle_name ISNOTNULLTHEN' '|| c.middle_name ELSE''ENDAS full_name, a.city, a.state_province, t.country_code ||'-('|| t.area_code ||') '|| t.telephone_number AS telephoneFROM member m INNERJOIN contact c ON m.member_id = c.member_id INNERJOIN address a ON c.contact_id = a.contact_id INNERJOIN street_address sa ON a.address_id = sa.address_id INNERJOIN telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_idWHERE c.last_name ='Winn';

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)

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.

Read 17 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.