By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table users (id int);
CREATE TABLE
insert into users
values (100),(101),(102),(103);
INSERT 0 4
create table users_2(id int)
CREATE TABLE
insert into users_2
values (100),(101),(201),(202);
INSERT 0 4
select id, case when users.id is null then 2 else 1 end as category
from users
full outer join users_2 using (id);
| id | category |
|---|---|
| 100 | 1 |
| 101 | 1 |
| 102 | 1 |
| 103 | 1 |
| 201 | 2 |
| 202 | 2 |
SELECT 6
select id, 1 as category
from users
union all
select id, 2
from users_2 where not exists (select id from users where id = users_2.id);
| id | category |
|---|---|
| 100 | 1 |
| 101 | 1 |
| 102 | 1 |
| 103 | 1 |
| 201 | 2 |
| 202 | 2 |
SELECT 6
select distinct on (id) id, category
from (
select id, 1 as category
from users
union all
select id, 2
from users_2
) x
order by id, category;
| id | category |
|---|---|
| 100 | 1 |
| 101 | 1 |
| 102 | 1 |
| 103 | 1 |
| 201 | 2 |
| 202 | 2 |
SELECT 6