From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gauthier, Dave
Sent: Tuesday, October 23, 2012 3:31 PM
To: pgsql-general@postgresql.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.broersma@gmail.com]
Sent: Tuesday, October 23, 2012 3:24 PM
To: chris@chriscurvey.com
Cc: Gauthier, Dave; pgsql-general@postgresql.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.broersma@gmail.com> wrote:
oops - I had some malformed air code
SELECT my_ids.id, ARRAY_AGG( ( property, value ) order by (property, value) )
FROM my_ids
--
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.
