CODEWITHSUNDEEP
databasepostgresqlselectconditional-statements
user1410747
user1410747

Reputation: 99

How to pull specific fields conditionally in PostgreSQL

Not entirely sure how to phrase this, but hope someone can help. Here's my problem.

I have a table T which has the following fields

ID      CF    Value
------------------------
976     13    Severity 1
978     36    branch a  
978     13    severity 1

I want to pull out items which have the attributes branch a and severity 1.

The problem is, the value field is used for both branch and severity, only differentiated by a different CF value of 13 or 36. If an item has both attributes, it is given a duplicate entry (for example ID 978 above)

So I can't select from D where value = branch a AND value = severity1.

What I want to do is a Select - As - From with the following fields

ID   Branch   Severity

And pull the value field into Branch if cf = 36 and pull value into Severity if cf = 13.

But I'm not sure how i'd do this syntactically. I've tried a few Cases and If statements but keep getting errors.

Any help much appreciated.

Thanks.

Upvotes: 0

Views: 248

Answers (3)

user330315
user330315

Reputation:

The standard approach for doing a pivot when having to cope with such a model is this:

select id, 
       max(branch) as branch,
       max(severity) as severity
from (
    select id, 
           case 
             when cf = 36 then value
             else null
           end as branch,
           case 
             when cf = 13 then value
             else null
           end as severity
    from t
) p
group by id

This type of "design" is called "entity-attribute-value" pattern and - as Catcall has already stated - is considered an anti-pattern. Precisely for the complicated way to retrieve things.

If you search for that term in the internet you'll get plenty of hits for this.

In PostgreSQL you could also use the crosstab function from the tablefunc module, which essentially creates the above statement dynamically inside a function.

http://www.postgresql.org/docs/current/static/tablefunc.html

Using the crosstab function, the query would look like this:

SELECT *
FROM crosstab(
  'select id, cf, value
   from t
   where cf in (13,36)
   order by 1,2'::text)
AS ct(id integer, severity text, branch text);

Upvotes: 1

araqnid
araqnid

Reputation: 133492

You can do simple operations with this sort of schema:

select id from t where cf = 36 and value = 'branch a'
intersect
select id from t where cf = 13 and value = 'severity1'

As Catcall already pointed out, this system can get pretty unwieldy after a while. If the id value in T refers so some entity table E, then you can create a view a bit like this:

create view e_attrs as
select e.id, t_36.value as branch, t_13.value as severity
from e
     left join t t_36 on t_36.id = e.id and t_36.cf = 36
     left join t t_13 on t_13.id = e.id and t_13.cf = 36

and then hope that join elimination (from postgresql 9.1, I think) will remove extraneous joins against T if you query on the view.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95572

You can get the ID numbers this way.

select id
from your_table
where value = 'branch a'
   or value = 'Severity 1'
group by id
having count(id) = 2

But in general you'll find that anything more complex will tie you in knots. This kind of structure is a well-known anti-pattern, and that's not an accident.

Upvotes: 0

Related Questions