Postgresql - Return empty string on left join instead of null-CodePudding

I have two tables :

Person table

idPerson (uuid)	name (varchar)
1	bob
2	sam

Telephone table

idTelephone (uuid)	number(varchar)	actif(boolean)	valid(boolean)	idPerson (uuid)
1	34343443	true	true	1
1	35676878	true	false	1
2	32432	false	false	2

SQL :

select p.idPerson, t.number
from person p
left join telephone t on t.idPerson = p.idPerson and t.actif=true and t.valid =true;

The above query will return only the first row for personId = 1.

Question : I want the query to return empty String for personId = 2. How can I achieve this please?

Please note that , a person can have multiple telephone numbers but only 1 valid and the remaining will be valid = false.

Current Result:

1     34343443

Expected Results:

1     34343443
2     ' '

CodePudding user response：

Your query will return a row for idperson number 2, but the number will be NULL.

To get an empty string instead, use

coalesce(t.number, '')

CodePudding user response：

you can use case when

   select p.idPerson, 
   case when t.number is null then ''' ''' else
   t.number end as nm
    from person p
    left join telephone t on t.idPerson = p.idPerson
     and t.actif=true and t.valid =true

demo link