COALESCE(FIELD, “DEFAULT VALUE”) not working for postgresql + rails

sql = "SELECT DISTINCT(p.*), concat(u.first_name,' ',u.last_name) as fullname, concat('@',u.username) as username, cover.file_path, cover.file_type, avatar.avatar_path 
       FROM products p
       JOIN stores s ON s.id = p.store_id
       JOIN users u ON u.id = s.user_id
       LEFT OUTER JOIN (SELECT fh.product_id AS cp_id, 
       coalesce(fh.file_path, 'default image path') AS file_path,
       fh.file_type AS file_type FROM file_handlers fh WHERE is_cover = true) cover ON p.id = cover.cp_id
       LEFT OUTER JOIN (SELECT fh.id AS av_id, fh.file_path AS avatar_path FROM file_handlers fh WHERE is_cover = false) avatar ON u.avatar_id = avatar.av_id"

Above is my raw sql query for my postgresql database. My problem is I want to display default file_path if it is null in the database. I tried three methods for this:

1. COALESCE(fh.file_path, 'default image') AS file_path,
2. COALESCE(NULLIF(fh.file_path, ''), 'default image') AS file_path,
3. CASE WHEN fh.file_path IS NULL THEN 'default image' ELSE fh.file_path END AS file_path,

No Luck!, none of them works for me, and I don’t know why. What am I missing here?

[NOTE: please see this screenshot]

Answer

I’m pretty certain your problem is due to the left join, rather than the coalesce. If the left join yields no row, cover.file_path ends up null regardless of the coalesce or not.

Try moving the coalesce statement to a more appropriate location:

sql = "SELECT DISTINCT(p.*), concat(u.first_name,' ',u.last_name) as fullname, concat('@',u.username) as username, coalesce(cover.file_path, 'default image path'), cover.file_type, avatar.avatar_path 
FROM products p
...

Advertisement

Answer