Consider this select statement:
SELECT *,
1 AS query_id
FROM players
WHERE username='foobar';
It returns the column query_id with value 1 along with a player's other columns.
How would one make the above SQL return at least the query_id of 1 even if the select finds no rows that match?
BTW, it's PostgreSQL 8.4.
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
i needed to do something similar and my brain was not giving, so i searched and found this question ... but then thought of this way also, if username is unique in players ... which in my use fits better (but surely it can be simplified more ?! :)
WITH _ AS ( SELECT * FROM players
WHERE username = 'foobar' )
, __ AS ( SELECT CASE WHEN EXISTS ( TABLE _ )
THEN ( SELECT _::players FROM _ )
ELSE ( SELECT NULL::players )
END _ )
SELECT (__._).*
, 1 AS query_id
FROM __
;
