Getting SELECT to return a constant value even if zero rows match
SELECT col1,
col2,
col3,
1 AS query_id
FROM players
WHERE username='foobar'
union all
select null,
null,
null,
1
where not exists (select 1 from players where username = 'foobar');
Or as an alternative (might be faster as no second subselect is required):
with qid (query_id) as (
values (1)
)
select p.*,
qid.query_id
from qid
left join players as p on (p.useranme = 'foobar');
You can re-write the above to a more "compact" representation:
select p.*,
qid.query_id
from (values (1)) as qid (query_id)
left join players as p on (p.useranme = 'foobar');
But I think the explicit CTE (with...
) is more readable (although that is always in the eyes of the beholder).
If you are only expecting one or zero rows back, then this would also work:
SELECT
max(col1) col1,
max(col2) col2,
1 AS query_id
FROM
players
WHERE
username='foobar';
This will return one row with all values having null except query_id if no row is found.
Chiming in way late here, but here's a syntax that works (at least in 9.2, haven't tried earlier versions).
SELECT (COALESCE(a.*,b.*::players)).*
FROM ( SELECT col1, col2, col3, 1 AS query_id
FROM players WHERE username='foobar' ) a
RIGHT JOIN (select null col1, null col2, null col3, 1 col4) b
ON a.query_id = b.col4;
Will only return the "blank" row if the entire contents of "a" is null.
Enjoy. /bithead