From: | Jonathan Moules <jonathan-lists(at)lightpear(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Always getting back a row, even with no results |
Date: | 2017-08-11 13:57:39 |
Message-ID: | 15dd19520d2.c2f4c716103038.4559674295652037989@lightpear.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi List,
I have a simple table:
CREATE TABLE my_table
(
id integer NOT NULL UNIQUE,
cat integer
);
insert into my_table (1, 2);
insert into my_table (2, 2);
insert into my_table (3, 3);
insert into my_table (4, 3);
insert into my_table (5, 2);
I want do a very basic query:
select id from my_table where cat = 3
|id|
|3|
|4|
This will of course return the two rows with that category. But I also want to be able to run the query with a non-existent cat and get a result of "null" for the id.
select id from my_table where cat = 500
would return
|id|
|NULL|
now I can do that with a union all:
select id from my_table where cat = 500
union all
select
NULL as id
|id|
|NULL|
But if I then run that query using a cat value of "3", it will not only return the results, but a third result, of NULL, which I don't want.
|id|
|3|
|4|
|NULL|
I would like to always get a result, either of NULL, or if there are actual results, or those actual results without a NULL if they exist.
I don't see it being possible with any of the coalesce/ifnull/case features, as they only action based on a single row and this can return multiple rows. Maybe something with the window functions or a CTE, but they're both new to me.
Is this possible?
Thanks
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2017-08-11 14:28:47 | Re: Always getting back a row, even with no results |
Previous Message | Steve Midgley | 2017-08-10 22:11:30 | Re: Replication and Field Level Encryption |