Re: INSERT ... SELECT DISTINCT - Doesn't work...

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> "Cesar A. K. Grossmann" wrote:
>> I think the DISTINCT clause, when used in a INSERT INTO ... SELECT
>> doesn't have any effect... Is it a bug?

> Hmm. I can't repeat this behavior in 7.0.0beta3. Are you using
> the older 6.x series?

INSERT ... SELECT DISTINCT sort of works in 6.5 (I think it might have
been completely broken in the distant past). The example Cesar gave
looked safe enough, but you can get burnt by the problem that what's
actually "DISTINCT'd" is the completed tuples ready to be inserted in
the target table. So, for example,
CREATE TABLE dest (f1 int, f2 serial);
INSERT INTO dest(f1) SELECT DISTINCT f1 FROM src;
won't do what it should because the f2 values are distinct.

7.0 fixes that problem, but it still has nasty bugs if the destination
column datatypes aren't the same as those of the source data you
are DISTINCT'ing. The underlying problem here is that we really need
a two-level querytree representation, so that the SELECT stuff can
happen separately from preparing the data to be INSERTed. We intend
to fix all this in the 7.2 development cycle; there's a wholesale
querytree redesign planned for that release.

Cesar's example looked like it didn't run into these problems, so I'm
not sure what's going wrong for him.

regards, tom lane