How to write postgres query for below issue?

Question

      Level1         |    Level2      |     Level3          |     Level4
key       |  value   | key     |value |    key     | value  |  key     | value
---------------------|----------------|---------------------|-----------------  
setting1  |  true    |                |  setting1  |  true  |setting1  | false
                     |setting2 | false|                     |setting2  | false    
                     |                |                     |setting3  | true

I have 4 tables. Level1, Level2, Level3, Level4. Each has key value pair.

I want to get key, value pair such that if key value pair is present in level1 then it should not check level2,3,4.

If it's not present in level1 then it should go to level2 if not then level3.

Value will always be present in level4.

So final o/p is of type map.

key       |  value
---------------------
setting1  |  true
setting2  |  false
setting3  |  true

Is it possible with sql query or do I need to write function or procedure for it?

I created an SQLFiddle for this here: sqlfiddle.com/#!12/560b2/1 — Angelo Fuchs
– Angelo Fuchs, Commented Apr 25, 2014 at 9:23

gpeche · Accepted Answer · 2014-04-25 09:39:50Z

1

SQL is declarative so it is hard to do side effects in a query: if you need that exact behaviour you should implement it in a procedural language.

If all you care about is that the query is functionally equivalent to what you described, you can use the following query:

    select key, value from (
        select 1 as precedence, key, value from t1
        union all
        select 2 as precedence, key, value from t2
        union all
        select 3 as precedence, key, value from t3
        union all
        select 4 as precedence, key, value from t4
    )
    where key = <searched key>
    order by precedence asc
    limit 1

However, the side effect of only accessing the tables as needed is not guaranteed: the SQL engine is free to access the tables whenever it thinks it's appropiate

answered Apr 25, 2014 at 9:39

gpeche

22.7k5 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard Huxton · Accepted Answer · 2014-04-25 10:55:04Z

You want DISTINCT ON with UNION ALL (although a straight UNION would work here too)

BEGIN;

CREATE TEMP TABLE settings1 (key text, value text, PRIMARY KEY (key));
CREATE TEMP TABLE settings2 (key text, value text, PRIMARY KEY (key));
CREATE TEMP TABLE settings3 (key text, value text, PRIMARY KEY (key));

INSERT INTO settings1 VALUES ('a', 'a1');
INSERT INTO settings2 VALUES ('a', 'a2');
INSERT INTO settings2 VALUES ('b', 'b2');
INSERT INTO settings3 VALUES ('b', 'b3');
INSERT INTO settings3 VALUES ('c', 'c3');

SELECT DISTINCT ON (key) key, value FROM (
    SELECT 1 AS lvl, key, value FROM settings1
    UNION ALL
    SELECT 2 AS lvl, key, value FROM settings2
    UNION ALL
    SELECT 3 AS lvl, key, value FROM settings3
    ORDER BY key, lvl
) AS settings;

ROLLBACK;

Gives:

 key | value 
-----+-------
 a   | a1
 b   | b2
 c   | c3
(3 rows)

Angelo Fuchs · Accepted Answer · 2014-04-25 09:22:52Z

0

One approach would be to use CASE - WHEN structure.

See this SQLFiddle

SELECT CASE WHEN t1."key" IS NOT NULL
            THEN t1."key"
            ELSE CASE WHEN t2."key" IS NOT NULL
                      THEN t2."key"
                      ELSE CASE WHEN t3."key" IS NOT NULL
                                THEN t3."key"
                                ELSE t4."key"
                           END
                 END
       END
       as "key",
       CASE WHEN t1."value" IS NOT NULL
            THEN t1."value"
            ELSE CASE WHEN t2."value" IS NOT NULL
                      THEN t2."value"
                      ELSE CASE WHEN t3."value" IS NOT NULL
                                THEN t3."value"
                                ELSE t4."value"
                           END
                 END
       END
       as "value"
FROM table4 t4
LEFT JOIN table1 t1 ON t4."key" = t1."key"
LEFT JOIN table2 t2 ON t4."key" = t2."key"
LEFT JOIN table3 t3 ON t4."key" = t3."key"

edited Apr 25, 2014 at 9:22

answered Apr 25, 2014 at 9:15

Angelo Fuchs

9,9911 gold badge39 silver badges73 bronze badges

1 Comment

user1298426 Over a year ago

Cool... let me try that.

Collectives™ on Stack Overflow

How to write postgres query for below issue?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related