sberry sberry - 3 months ago 7
SQL Question

Determine source on COALESCE fields

I have two tables

table
which are identical in structure but belong to different schemas (schemas
A
and
B
). All rows in question will always appear in the
A.table
but may or may not appear in
B.table
.
B.table
is essentially an override for the defaults in
A.table
.

As such my query uses a COALESCE on each field similar to:

SELECT COALESCE(B.id, A.id) as id,
COALESCE(B.foo, A.foo) as foo,
COALESCE(B.bar, A.bar) as bar
FROM A.table LEFT JOIN B.table ON (A.id = B.id)
WHERE A.id in (1, 2, 3)


This works great, but I also want to add the source of the data. In the example above, assuming
id=2
existed in
B.table
but not 1 or 3, I would want to include some indication that A is the source for 1 and 3 and B is the source for 2.

So the data might look like the following

+---------------------------------+
| id | foo | bar | source |
+---------------------------------+
| 1 | a | b | A |
| 2 | c | d | B |
| 3 | e | f | A |
+---------------------------------+


I don't really care what the value of source is as long as I can distinguish A from B.

I am no pgsql expert (not by a long shot) but I have tinkered around with EXISTS and a subquery but have had no luck so far.

Answer

As records showing the default value (from A.table) have NULLs for B.id, all you need is to add this column specification to your query:

CASE WHEN B.id IS NULL THEN 'A' ELSE 'B' END AS Source