Q:

Postgres: check returns from xpath()

I have an SQL statement like such: 
select coalesce(nullif(xpath('//consumer/contact_address/\@postcode', xml),'{}'), '') from consumer where docid = 12345; 

It gives me an error: 
ERROR:  array value must start with "{" or dimension information

What does that even mean? Also, if I try it using the psql interactive prompt, I get another cryptic error: 
#psql: select nullif(xpath('//consumer/contact_address/\@street', xml),  '{}') from consumer where docid = 12345;
WARNING:  nonstandard use of escape in a string literal
LINE 1: select nullif(xpath('//consumer/contact_address/\@street', x...
                        ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR:  could not identify an equality operator for type xml

What's wrong here? Thanks! 

A:

You want:
select coalesce(nullif(xpath('//consumer/contact_address/\@postcode', xml),'{}'::xml[]), '') 
  from consumer 
 where docid = 12345; 

Note the cast to the xml array, but unfortunately there is not equality operator for xml[]s. So you will have to do this:
select CASE WHEN array_upper(xpath('//consumer/contact_address/\@postcode', xml)) = 1 
            THEN xpath('//consumer/contact_address/\@postcode', xml) END
  from consumer 
 where docid = 12345; 

Untested, play with the upper bound, maybe you have to check agains zero. Don't worry, PostgreSQL detects similar subformulars and evaluates the xpath expression only once.
The warning results in using Escape characters in String literals, what is bot SQL conform, but posible. Use E'Next \n line' or disable this check in postgresql.conf!

