Re: union/difference/intersection question - Mailing list pgsql-general

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

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;

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

--

 David Rowley                   http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________