PostgreSQL: how to concat two character varying fields

Question

I have a table named res_partner in PostreSQL (v9.3) with the following columns:

phone              | character varying(64)       | 
mobile             | character varying(64)       |

I would like to concatenate both strings in the following way: "phone/mobile"

E.g:

+34942246573 / +3466243765

I'm trying the following in a SQL query:

SELECT (phone || '/' || mobile) AS PhoneNumbers  FROM res_partner;

The query works fine when the person has both 'phone' and 'mobile' populated:

myDB=# SELECT id, (phone || '/' || mobile) AS PhoneNumbers FROM res_partner WHERE id=43;
 id |    phonenumbers     
----+---------------------
 43 | 942686868/666666666

But if the person has only phone or mobile (but not both) populated, all I get is an emtpy string:

myDB=# SELECT id, (phone || '/' || mobile) AS PhoneNumbers FROM res_partner WHERE id=21;
 id | phonenumbers  
----+--------------
 21 | 
(1 fila)

But in this case I would expect to show something as "+54 341 324 9459 / " for PhoneNumbers

Why is showing an empty string at the output?

user330315 · Accepted Answer · 2014-02-11 20:42:31Z

4

That works according to the SQL standard. Any expression involving NULL yields null.

You can use the "null-safe" concat() function or probably better in your case concat_ws() which also takes care of not adding the separator is one of the inputs is null:

concat_ws('/', phone, mobile);

More details in the manual: http://www.postgresql.org/docs/current/static/functions-string.html

answered Feb 11, 2014 at 20:42

user330315

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2014-02-11 20:43:41Z

1

The result of you query is not an empty string - it's null, which is the result of performing any string operation on a null value. To avoid this, you could coalesce the column in your query, and replace any nulls with empty strings:

SELECT (COALESCE(phone, '') || '/' || COALESCE(mobile, '')) AS PhoneNumbers
FROM   res_partner;

answered Feb 11, 2014 at 20:43

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: how to concat two character varying fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related