Re: Need sql to pull data from terribly architected table

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Gauthier, Dave
Sent: Tuesday, October 23, 2012 3:31 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Need sql to pull data from terribly architected table

Thanks for the answers. But I also have a predicate...

select col1,col2 from foo where col4='c4' and col5 <> 'xxx';

How is that done?

From: Richard Broersma [mailto:richard(dot)broersma(at)gmail(dot)com]
Sent: Tuesday, October 23, 2012 3:24 PM
To: chris(at)chriscurvey(dot)com
Cc: Gauthier, Dave; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Need sql to pull data from terribly architected table

On Tue, Oct 23, 2012 at 12:21 PM, Richard Broersma
<richard(dot)broersma(at)gmail(dot)com> wrote:

On Tue, Oct 23, 2012 at 12:06 PM, Chris Curvey <chris(at)chriscurvey(dot)com>
wrote:

select my_ids.id

, c1.value as col1

, c2.value as col2

, c3.value as col3

, c4.value as col4

, c5.value as col5

from my_ids

left join foo c1 on my_ids.id = c1.id

left join foo c2 on my_ids.id = c2.id

left join foo c3 on my_ids.id = c3.id

left join foo c4 on my_ids.id = c4.id

left join foo c5 on my_ids.id = c5.id

How about:

oops - I had some malformed air code

SELECT my_ids.id, ARRAY_AGG( ( property, value ) order by (property, value)
)
FROM my_ids

GROUP BY id

ORDER BY id;

--
Regards,
Richard Broersma Jr.

--
Regards,
Richard Broersma Jr.

You put the above query into a sub-select or CTE (WITH) and then in the
outer query you apply whatever where clause you want.

If you want to try and help the planner you could do:

SELECT some_id FROM foo WHERE property = '.' AND value = '.'

UNION ALL

SELECT some_id FROM foo WHERE property = '.' AND value = '.'

To pre-define which IDs are candidates and then use that information later
on in the query.

Whether this is a worthwhile effort I have no idea and it may not matter
anyway depending on how well the brute-force approach works given your data.

David J.