CODEWITHSUNDEEP
postgresqlview
user3851177
user3851177

Reputation: 45

How to return null value as text 'N/A' in a postgresql view that uses concat_ws

I have 5 columns that I need to query using concat_ws (to make text for kml file that only has one description field and I need info from 5 columns), and in between the concatenated columns I want to add text before two columns: 

SELECT concat_ws(' '::text, col1, col2, col3, 
'text_for_4:', col4, 
'text_for_5:', col5) AS concat_ws,..

This works fine, but can I create a query that will return a text of 'N/A' if a row has a null value? I.e. if col4 is null the return would be 'col1 col2 col3 text_for_4: N/A text_for_5: col5'

Upvotes: 0

Views: 4504

Answers (2)

Politank-Z
Politank-Z

Reputation: 3719

Use COALESCE:

SELECT concat_ws(' '::text, COALESCE ( col1, 'N/A' ), COALESCE ( col2, 'N/A' ),
COALESCE ( col3, 'N/A' ), 
'text_for_4:', COALESCE ( col4, 'N/A' ), 
'text_for_5:', COALESCE ( col5, 'N/A' )) AS concat_ws,..

Upvotes: 5

quarterdome
quarterdome

Reputation: 1581

Use CASE statement (http://www.postgresql.org/docs/9.3/static/functions-conditional.html):

select 
  case 
    when column is null 
    then 'N/A'
    else column
  end
from table;

Upvotes: 1

Related Questions