Postgresql – A flag (indicator) that shows for each row which conditions was used to select it

conditionpostgresql

Suppose I have the table foo. I want to filter the rows of this table using different conditions (cond_1, cond_2, …, cond_n – the order is important) combined in a disjunction, i.e.:

SELECT * FROM foo WHERE cond_1 OR cond_2 OR ... OR cond_n;

Is it possible (in PostgreSQL 13) to distinguish for each row which condition cond_i was used to select it? Since I stated that the order is important the question may be reformulated as – I want to know the first cond_i that selected the row, for each row.

Now consider that I have in foo a column for this purpose, e.g. selected_by.

Is it possible to distinguish for each row which condition cond_i was used to select it and store it in selected_by? (the same as 1st question but for an UPDATE statement)

Two things are important: the order of conditions (as it was highlighted earlier); performance (i.e. table foo may have many rows, the number of conditions is small, up to 5-10).

Best Answer

If your conditions are mutually exclusive (you you only care about matching the first of an order you control) then you could get away with a single column:

SELECT *,
  CASE 
    WHEN name = 'meatfeast' THEN 'name' 
    WHEN diet = 'vegan' THEN 'diet' 
  END as reason_for_appearing
FROM
  pizza
WHERE
  name = 'meatfeast' OR diet = 'vegan'

You repeat your where conditions as individual clauses of a case when, (in the order you care about them)

If they aren't mutually exclusive and you want to know all the reasons why it might have matched you'll either need multiple columns ..

SELECT *,
  CASE WHEN name = 'john' THEN 'yes' END as because_of_name,
  CASE WHEN city = 'Chicago' THEN 'yes' END as because_of_city
FROM
  person
WHERE 
  name = 'john' OR city = 'chicago'

.. unless you want to get really involved with a huge case when of all the conditions combinations (which is 2^N, as I'm sure you can imagine)

Or you might alternatively do some kind of concat you can pick apart programmatically or the user can read

SELECT *,
  CONCAT(
    CASE WHEN name = 'john' THEN 'name' END,
    CASE WHEN city = 'Chicago' THEN ' city' END 
  ) as because_of
FROM
  person
WHERE 
  name = 'john' OR city = 'chicago'

I suppose it rather depends on what you want to do with it after

Related Solutions

Postgresql – Performing SELECT on EACH ROW in CTE or Nested QUERY

Let me know if this helps. This is using MSSQL (T-SQL) syntax so you might have to adjust it for postgres, but, using a temp table called #tree, containing the two columns id and parent and populated as your example is with

SELECT 1, 0
UNION 
SELECT 2, 1
UNION 
SELECT 3, 1
UNION
SELECT 4, 2
UNION 
SELECT 5, 2
UNION 
SELECT 6, 4
UNION
SELECT 7, 6
UNION 
SELECT 8, 6:

with cte as
(
    select id as currentnode, id as root
    from #tree
    union all
    select t.id as currentnode, cte.root 
    from #tree t join cte on t.parent = cte.currentnode
)
select *
from cte
order by root

The above recursive CTE will yield all nodes reachable starting from some root. You can do:

SELECT root, count(*)
from cte
group by root

The count(*) query should yield,

root   |  count
----------------
  1    |    8
  2    |    6
  3    |    1
  4    |    4
  5    |    1
  6    |    3
  7    |    1
  8    |    1

You could then subtract 1 from each of those to get the result you were hinting at. Is this what you were looking for? Even if not, I feel like a recursive CTE can probably help with what you need.

Postgresql – Calculate value differences of rows before and after bounded by multiple referenced rows

SELECT *
      ,CASE WHEN location = 'Loc_B' OR right_tree = 0 AND left_tree = 0 THEN NULL::int
            ELSE CASE WHEN @right_diff < @left_diff THEN right_diff ELSE left_diff END
       END AS min_diff
FROM  (
   SELECT *
         ,CASE WHEN right_tree > 0 THEN start_time - right_end ELSE 1000 END AS right_diff
         ,CASE WHEN left_tree  > 0 THEN end_time  - left_start ELSE 1000 END AS left_diff
   FROM  (
      SELECT *
            ,first_value(end_time)   OVER (PARTITION BY right_tree ORDER BY start_time) AS right_end
            ,first_value(start_time) OVER (PARTITION BY left_tree  ORDER BY start_time DESC) AS left_start
      FROM  (
         SELECT *
               ,count(location = 'Loc_B' OR NULL) OVER (PARTITION BY id ORDER BY start_time) AS right_tree
               ,count(location = 'Loc_B' OR NULL) OVER (PARTITION BY id ORDER BY start_time DESC) AS left_tree
         FROM   travel
         ) a
      ) b
   ) c
   ORDER  BY id, start_time;

Produces your result exactly.

1000 is just some "higher value than any other". Since your actual problem seems to operate with times and intervals infinity would be the perfect choice.

Related answer with detailed explanation how groups (right_tree and left_tree here) are formed:
Select longest continuous sequence

SQL Fiddle.

Best Answer

Related Solutions

Postgresql – Performing SELECT on EACH ROW in CTE or Nested QUERY

Postgresql – Calculate value differences of rows before and after bounded by multiple referenced rows

Related Question