pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Lucas Adamski <ladamski@manageww.com>
To: 'Stephan Szabo' <sszabo@megazone23.bigpanda.com>
Cc: 'Postgresql Performance Mailing list (E-mail)' <pgsql-performance@postgresql.org>
Subject: Re: Hack around lack of CORRESPONDING BY in EXCEPT?
Date: Mon, 12 May 2003 00:29:50 -0700
Message-ID: <001c01c31858$46967500$11f5ec0c@LADAMSKI> (raw)
In-Reply-To: <20030507154625.C32502-100000@megazone23.bigpanda.com>

Stephan,

Bingo!  That worked perfectly, thank you!  I was considering something like
that, but couldn't figure out the syntax offhand to join two events tables
in that fashion.  Didn't realize you could alias a table as well!  Thanks
again,
  Lucas.

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Wednesday, May 07, 2003 3:59 PM
> To: Lucas Adamski
> Cc: Postgresql Performance Mailing list (E-mail)
> Subject: Re: [PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT?
>
>
>
> On Wed, 7 May 2003, Lucas Adamski wrote:
>
> > I wrote it originally as:
> >
> > SELECT tracking.pk,events.data1,events.data2 FROM
> tracking,events WHERE
> > tracking.event_fk = event.pk EXCEPT (SELECT
> events.data1,events.data2 FROM
> > events WHERE event.type = 10)
> >
> > because each of these subqueries restricts the dataset
> greatly before doing
> > the join.  I've simplified the actual problem (as the real
> code has a bunch
> > of extraneous stuff that makes it even more obtuse), but
> essentially, the
> > tracking table maintains a record of the last record type
> that was entered.
> > The type is incremented for each batch of events that is
> loaded.  In this
> > case, I'm assuming that the latest batch is type=10 (or
> 5000, or 100000),
> > and the tracking table references a small subset  of previous events
> > (possibly of types 1-9 in this example).  This particular
> query is supposed
> > to return all tracking.pk's that are present in the
> previous batches (types)
> > but not in the latest batch (10).  I didn't mean to make it
> quite so obtuse,
> > sorry. :)
>
> Maybe something like nominally like (quickly done so possibly wrong
> again):
>
>  select tracking.pk, events.data1, events.data2 from
>   tracking,events where not exists (select * from events e where
>   e.type=10 and e.data1=events.data1 and e.data2=events.data2)
>   and tracking.event_fk=event.pk
>
> Get all tracking/event combinations, not including those
> where the data1/2
> matches that of an event with type 10.
>
> That might give dups if there are multiple events rows with
> that pk for
> different types (but not 10).
>
>




view thread (11+ messages)

Message-ID: <001c01c31858$46967500$11f5ec0c@LADAMSKI>
Permalink:  ../001c01c31858$46967500$11f5ec0c@LADAMSKI/
Also on:    postgresql.org/message-id/001c01c31858$46967500$11f5ec0c@LADAMSKI

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: ladamski@manageww.com, sszabo@megazone23.bigpanda.com
  Subject: Re: Hack around lack of CORRESPONDING BY in EXCEPT?
  In-Reply-To: <001c01c31858$46967500$11f5ec0c@LADAMSKI>
  References: <20030507154625.C32502-100000@megazone23.bigpanda.com> <001c01c31858$46967500$11f5ec0c@LADAMSKI>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox