3

I've got (key, value) pairs stored in two different tables - company_settings and branch_settings. A key can exist in none, one or both of the tables.

In case the key exists in both tables, I'd like to use the value stored in the branch_settings table. Otherwise, if available, I'd like to fall back to the value stored in the company_settings table.

company_settings
-------------------------
| key    | value
-------------------------
| key.A  | 4             |
-------------------------
| key.B  | 5             |
-------------------------


branch_settings
-------------------------
| key    | value
-------------------------
| key.A  | 1             |
-------------------------

So, if I query for key.A the result should be 1 since key.A exists in both tables and I want to give higher precedence to records from branch_settings table.

If I query for key.B the result should be 5 since key.B exists only in company_settings table.

3

If you need the value for only one "key", you can use COALESCE() function with 2 subqueries:

-- query 1a
SELECT COALESCE(
           (SELECT value FROM branch_settings WHERE key = prm.key),
           (SELECT value FROM company_settings WHERE key = prm.key)
       ) AS value 
FROM
    (VALUES ('key.A')) AS prm (key) ;

You could also expand the list of keys inside the VALUES, to search more than one key:

-- query 1b
FROM
    (VALUES ('key.A'), ('key.B'), ..., ('key.Z')) AS prm (key) ;

If you want to search many keys however, it might be more efficient to convert the subqueries to joins. Either two LEFT joins:

-- query 2
SELECT prm.key, COALESCE(b.value, c.value) AS value 
FROM
    (VALUES ('key.A'), ('key.B'), ..., ('key.Z')) AS prm (key)
  LEFT JOIN branch_settings AS b ON b.key = prm.key
  LEFT JOIN company_settings AS c ON c.key = prm.key ;

or a FULL JOIN, similar to @James' answer:

-- query 3
SELECT key, COALESCE(b.value, c.value) AS value 
FROM
    (SELECT * FROM branch_settings 
     WHERE key IN ('key.A', 'key.B', ..., 'key.Z')
    ) AS b
  FULL JOIN 
    (SELECT * FROM company_settings 
     WHERE key IN ('key.A', 'key.B', ..., 'key.Z')
    ) AS b
  USING (key) ;

There is a subtle difference between queries 1b, 2 and query 3. The last query will give you in the results only the (parameter) keys that appear in at least one of the tables. Query 1b and 2 will have all the (parameter) keys in the results, with NULL in value for keys that can'e be found in either table.

  • COALESCE is brilliant. However, I don't understand what (VALUES ('key.A')) AS prm (key) is doing? – Code Poet Dec 20 '17 at 20:21
  • 1
    It's just a way to pass the parameter once (so it isn't in the two WHERE clauses). You could also expand the list of keys inside the VALUES, to more than one. – ypercubeᵀᴹ Dec 20 '17 at 20:22
  • If you only need 1 row, won't the JOIN be just as efficient as the subqueries? In which case, I'd favor the improved readability and flexibility of the JOIN. – jpmc26 Dec 21 '17 at 4:00
2

This should work in any RDBMS (only with the assumption that branch keys appear in company table, with is not correct, see comments and below):

SELECT cs.key AS key, 
       CASE WHEN bs.key IS NULL THEN cs.value ELSE bs.value END AS value
FROM company_settings AS cs
LEFT OUTER JOIN branch_settings AS bs ON (cs.key=bs.key)
WHERE cs.key='whatever'

Also, if you are still on design stage, have a look at PostgreSQL inheritance here: https://www.postgresql.org/docs/10/static/ddl-inherit.html . Be wary of the various drawbacks detailed, but otherwise your use case could be a good fit to it.

EDIT taking into account that a branch key may not appear at all in the company settings key, this would be better suited:

SELECT key, value FROM
(
    SELECT 1 AS p, key, value FROM branch_settings WHERE key='whatever'
    UNION ALL
    SELECT 2 AS p, key, value FROM company_settings WHERE key='whatever'
) AS merge
ORDER BY p ASC
LIMIT 1

or

SELECT CASE WHEN cs.key IS NULL THEN bs.key ELSE cs.key END AS key,
       CASE WHEN bs.key IS NULL THEN cs.value ELSE bs.value END AS value
FROM company_settings AS cs
FULL OUTER JOIN branch_settings AS bs ON (cs.key=bs.key)
WHERE bs.key='whatever' OR cs.key='whatever';

And here is an example of using inheritance in this case (may probably not be the smarter way, it works better in the first assumption above)

test=# CREATE TABLE company_settings (key text, value text, pos int DEFAULT 1);
CREATE TABLE
test=# CREATE TABLE branch_settings (pos int DEFAULT 2) INHERITS (company_settings);
NOTICE:  merging column "pos" with inherited definition
CREATE TABLE
test=# INSERT INTO company_settings VALUES ('A', 4);
INSERT 0 1
test=# INSERT INTO company_settings VALUES ('B', 5);
INSERT 0 1
test=# INSERT INTO branch_settings VALUES ('C', 6);
INSERT 0 1
test=# INSERT INTO branch_settings VALUES ('A', 1);
INSERT 0 1
test=# SELECT key, value FROM company_settings WHERE key='A' ORDER BY pos DESC LIMIT 1;
 key | value
-----+-------
 A   | 1
(1 row)

test=# SELECT key, value FROM company_settings WHERE key='B' ORDER BY pos DESC LIMIT 1;
 key | value
-----+-------
 B   | 5
(1 row)

test=# SELECT key, value FROM company_settings WHERE key='C' ORDER BY pos DESC LIMIT 1;
 key | value
-----+-------
 C   | 6
(1 row)

test=# SELECT key, value FROM company_settings WHERE key='D' ORDER BY pos DESC LIMIT 1;
 key | value
-----+-------
(0 rows)
  • 1
    Very interesting suggestion! Suppose branch_settings inherits from company_settings. I have this key key.A in both tables. Now, I'd obviously query company_settings when looking for a key. What happens when the key is defined in both? I will get two results, right? – Code Poet Dec 20 '17 at 20:18
  • 2
    No, you'll get one. But this query will give you no result if a key appears only in branch settings and not in company settings. – ypercubeᵀᴹ Dec 20 '17 at 20:23
  • @yper-troll I supposed that branch settings only overrode what is in company settings so that all keys in branch settings are supposed to be in company settings. Rereading the question, it seems not to be the case, so sorry wrong reply indeed. – Patrick Mevzek Dec 20 '17 at 20:28
  • @PatrickMevzek the data shown agree with your assumption. Lets wait for the OP to clarify. – ypercubeᵀᴹ Dec 20 '17 at 20:34
  • @yper-troll, I added another way more correct. there was not enough data to resolve all special cases :-) – Patrick Mevzek Dec 20 '17 at 20:37
2

For a single key value

So, if I query for key.A ...

Sounds like you typically query for a single key value. The simplest and fastest way to do this has not been posted, yet:

             SELECT value FROM branch_settings  WHERE key = 'key.A'
   UNION ALL SELECT value FROM company_settings WHERE key = 'key.A'
-- UNION ALL SELECT 0  -- optional default (with matching type!)
   LIMIT 1;

Postgres will stop executing as soon as the first row is found. Testing with EXPLAIN ANALYZE, you'll see (never executed) for the second query if the first query returns a row.

The third SELECT is totally optional, but a cheap and elegant way to default to value if the key is in neither of the two tables.

Note a subtle difference to using COALESCE((SELECT ..), (SELECT ..)): My query returns whatever is found in the column if the row exists - including potential NULL values, which can happen if the column value isn't defined NOT NULL. COALESCE, on the other hand, cannot distinguish between two distinct cases of NULL:

  1. The search for 'key.A' returned no row.
  2. The search for 'key.A' returned a row but the value is NULL.

You may or may not want to lump both cases together. But be aware of the difference.

Related:

For multiple key values

Nested in a LATERAL subquery, the same technique works for any number of values:

SELECT k.key, v.value
FROM  (VALUES ('foo'::text), ('bar'), ('baz')) k(key)  -- could be a table or query too
LEFT   JOIN LATERAL (
             SELECT value FROM branch_settings  WHERE key = k.key
   UNION ALL SELECT value FROM company_settings WHERE key = k.key
   -- UNION ALL SELECT 0
   LIMIT 1
   ) v ON true;

In a cursory test, this was several times faster than LEFT JOIN + COALESCE for 500 keys. It reduces work for Postgres to a minimum.

LEFT JOIN LATERAL (...) ON true retains key values without match (which can only happen without the commented last SELECT). To drop such rows instead:

SELECT k.key, v.value
FROM   k, LATERAL (
             SELECT value FROM branch_settings  WHERE key = k.key
   UNION ALL SELECT value FROM company_settings WHERE key = k.key
   LIMIT 1
   ) v;

You still get NULL in value for actual NULL values in branch_settings.value or company_settings.value. Adapt the query to handle distinct cases any way you need.

  • Nice and very simple indeed (slaps forehead). In the simplest case, yes, I do query a single key but on occasions, it's a little more complicated. I'll see how I can use this knowledge. – Code Poet Dec 25 '17 at 14:30
  • 1
    @CodePoet: The technique can be used in more complicated situations as well. And there are benefits. I added more above. – Erwin Brandstetter Dec 27 '17 at 18:39
1

In Sql Server (probably, but not always the same as postgresql - for example, I had to use key_' and not 'key'), I'd try this:

select isnull(b.key_,c.key_) as key_, isnull(b.value, c.value) as value
from branch_settings b
full outer join company_settings c on b.key_ = c.key_ ;

I tested it like so:

create table company_settings (key_ varchar(255), value int) ;
create table branch_settings (key_ varchar(255), value int) ;

insert into company_settings (key_, value)
values
('Key.A',4),
('Key.B',5) ;

Insert into branch_settings (key_, value)
values ('Key.A',1) ;

select isnull(b.key_,c.key_) as key_, isnull(b.value, c.value) as value
from branch_settings b
full outer join company_settings c on b.key_ = c.key_ ;

And my results were

key_    value
Key.A   1
Key.B   5
1

Below are three more options... take a look at this SQL Fiddle to see them in action.

1. FULL OUTER JOIN

I find this the most elegant and sane. Performance is probably the best as well, but table/index size among lots of other factors will impact performance. The join may cost you more than other options, but I may choose this anyway.

SELECT
  key,
  coalesce(branch_settings.value, company_settings.value)
FROM
  company_settings
  FULL OUTER JOIN branch_settings USING (key);

Bonus, you can add your parameters in a single WHERE clause like any other query.

WHERE
  key = 'key.A' -- single
  OR key IN ('key.A', 'key.B', ..., 'key.Z') -- several

2. DISTINCT ON ...

This option works well and has the added benefit of removing the full outer join at the expense of adding the DISTINCT ON clause... not free, but may be better given other factors.

SELECT
  DISTINCT ON (
    key
  )
  key,
  value
FROM
  (
    SELECT 1 AS priority, key, value FROM branch_settings
    UNION ALL
    SELECT 2 AS priority, key, value FROM company_settings
  ) AS "sub"
ORDER BY
  key,
  priority;

3. COMBINING QUERIES

Depending on how often you have branch_settings overriding company_setting this could also be a viable option. The biggest downside is that it hits both tables twice. That said, if the second request is relatively limited it could be a viable option.

SELECT
  key,
  value
FROM
  branch_settings
UNION ALL
SELECT
  key,
  value
FROM
  (
    SELECT key FROM company_settings
    EXCEPT
    SELECT key FROM branch_settings
  ) AS "sub"
  JOIN company_settings USING (key);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.