可以在SELECT的单个示例中使用窗口函数和DISTINCT ON来完成：

SELECT DISTINCT ON (u.id)
       u.id, u.name, u.avatar
     , m.created_at  AS max_created_at
     , m.body        AS last_message
     , bool_and(is_read) FILTER (WHERE m.from_id <> 14)          OVER w AS is_read
     , count(*) FILTER (WHERE NOT m.is_read AND m.from_id <> 14) OVER w AS count_unread
FROM   messages m
JOIN   users    u ON u.id IN (m.from_id, m.to_id)
WHERE  14 IN (m.from_id, m.to_id)
AND    u.id <> 14
WINDOW w AS (PARTITION BY u.id)
ORDER  BY u.id, m.created_at DESC NULLS LAST, m.body DESC NULLS LAST;

相关：

• 是否选择每个GROUP BY组中的第一行？
• 在应用LIMIT之前获取结果计数的最佳方法

我想你需要这样：

select a.* from(
SELECT
    users.id,
    users.name,
    users.avatar,
    MAX(messages.created_at) max_created_at,
    messages.body   last_message,
    CASE WHEN(COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) = 0) THEN true ELSE false END is_read,
    COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) count_unread,
        RANK () OVER ( 
        PARTITION BY user_id
        ORDER BY messages.created_at DESC
    ) message_rank 
FROM 
    messages 
INNER JOIN 
    users ON messages.from_id = users.id OR messages.to_id = users.id
WHERE 
    (messages.from_id = 14 OR messages.to_id = 14) 
    AND users.id != 14
GROUP BY 
    users.id)a
where message_rank=1;