mysql - COALESCE(FIELD，“DEFAULT VALUE”)不适用于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"

上面是我对postgresql数据库的原始sql查询。我的问题是，如果数据库中的默认文件路径为空，我想显示它。为此，我尝试了三种方法：

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,

不走运！，它们都不适合我，我也不知道为什么。我错过了什么？
[注：请参见this screenshot]

最佳答案

我很确定你的问题是由于左连接，而不是合并。如果左连接不产生行，cover.file_path最终将为空，而不管是否合并。
尝试将coalesce语句移动到更合适的位置：

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
...