aggregation question

On 9 December 2015 at 00:44, Chris Withers <chris@simplistix.co.uk> wrote:

Hi All,

I hope this is quite a simple one...

I have a fixtures table containing home_club and away_club, and I'd like
to select the distinct list of clubs contained in all rows but in either
the home_club or away_club columns.

How would I do that?

Use UNION:

select home_club from fixtures UNION select away_club from fixtures;

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 08/12/2015 11:51, David Rowley wrote:

On 9 December 2015 at 00:44, Chris Withers <chris@simplistix.co.uk
<mailto:chris@simplistix.co.uk>> wrote:

Hi All,

I hope this is quite a simple one...

I have a fixtures table containing home_club and away_club, and
I'd like to select the distinct list of clubs contained in all
rows but in either the home_club or away_club columns.

How would I do that?

Use UNION:

select home_club from fixtures UNION select away_club from fixtures;

Fantastic, thanks.

That reminds me of a more general question, say I have a query which
returns:

key='a', value=1
key='b', value=2

...and another query, which returns:

key='b', value=3
key='c, value=4

(obviously the real world version of this has many more rows, and
indeed, more key columns and more value columns...)

So, ultimately I want to turn these two queries into inserts for keys
like 'a', updates for keys like 'b' and deletes for keys like 'c'.
What's the most efficient way of doing this? Three queries to get the
three sets, then a bulk insert, a set of updates and a bulk delete?

cheers,

Chris