PostgreSQL Column Priority Search – How to Implement Efficiently

postgresql

Description:

We are using PostgreSQL 9.3 – Centos 6 x64 . We have a dtsc_search_data table as below: 

dtsc_search_data 
id ---- c1 ---- c2 ---- c3
1  ----  1 ----  1 ----  1 
2  ----  1 ----  2 ----  2 
3  ----  1 ----  1 ----  3

We want to search on "c1, c2, c3" coloumns on dtsc_search_data table with conditions: if seach_value is found on "c1" then return ; if search_value is not found on "c1" then finding on "c2", if search_value is found on "c2" then return ; else return (c3).

Example:

search_value = 1 => "c1" = 1 => just search on "c1"
search value = 2 => "c1" != 2, "c2" = 2 => just search on "c2". 
search_value = 3 => "c3"

CODE (update)

create table dtsc_search_data (id int, c1 int, c2 int , c3 int) ;
 insert into dtsc_search_data values(1,1,1,1);
 insert into dtsc_search_data values(2,1,2,2);
 insert into dtsc_search_data values(3,1,1,3);

 -- search_value = 2
 -- find on c1 column first
     select * 
     from dtsc_search_data
     where c1 = 2
 -- if c1 is not found then c2 -- get value from here
     select * 
     from dtsc_search_data
     where c2 = 2

Question:

Our solution: write 3 SQL queries search on "c1", "c2", "c3" are followed by conditions above. How can we do that with lowest performance ?

Best Answer

A simple OR condition will do this:

select * 
from dtsc_search_data
where c1 = 2
  or (c1 <> 2 and c2 = 2)
  or (c1 <> 2 and c2 <> 2 and c3 = 2);

SQLFiddle example: http://sqlfiddle.com/#!15/d1ca1/1

Related Question