PostgreSQL query returning NULL values when using COALESCE with subselect for default values
I'm working with PostgreSQL 14.1 and I have a query where I'm trying to fetch user data along with their last login date. However, I'm working with an scenario where the `COALESCE` function is returning NULL values for the last login date when I expect it to use a default value provided by a subselect. Here's the query I'm currently using: ```sql SELECT u.id, u.username, COALESCE( (SELECT l.last_login FROM logins l WHERE l.user_id = u.id ORDER BY l.login_time DESC LIMIT 1), '1970-01-01'::date) AS last_login FROM users u; ``` In this query, I want to return the latest login date for each user or '1970-01-01' if they have never logged in. However, I'm getting NULL for users who have never logged in instead of the default date. I tried breaking down the subquery to verify it retrieves values correctly, and it works fine when run independently: ```sql SELECT l.last_login FROM logins l WHERE l.user_id = 1 ORDER BY l.login_time DESC LIMIT 1; ``` This returns NULL when there are no records, which seems to propagate back to the main query. I also checked the data types and ensured that both the subquery and the COALESCE function are using compatible types. What am I doing wrong here? Is there a better pattern to achieve this result? Any suggestions on how to fix this or improve the query would be greatly appreciated!