Re: count (DISTINCT expression [ , ... ] ) and documentation

On Fri, Dec 26, 2008 at 08:03:30PM +0100, Ivan Sergio Borgonovo wrote:
> On Fri, 26 Dec 2008 10:43:25 -0800
> David Fetter <david(at)fetter(dot)org> wrote:
>
> > On Fri, Dec 26, 2008 at 03:34:33PM +0100, Ivan Sergio Borgonovo
> > wrote:
> > > I noticed that starting from 8.2 the documentation at
> > > http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html
> > > say that multiple distinct expressions are supported
> > >
> > > aggregate_name (DISTINCT expression [, expression] )
>
> > In 8.4, you'll be able to do:
>
> > WITH d AS (
> > SELECT DISTINCT c1, c2 FROM table1
> > )
> > SELECT count(*) FROM d;
>
> Nice, but what will be the difference from
> select count(*) from (select distinct c1, c2 from t);
> ?
> Optimisation?

None especially.

> Furthermore... I was actually looking at docs because I needed to
> find a way supported by both postgresql and mysql

Generally, it's *not* a good idea to try to support more than one
back-end. You wind up maintaining several disparate code bases, all
of which must do exactly the same thing, or you create your own RDBMS
in your client code, or worst of all, some of each.

Unless the most important attribute of the software, i.e. you can
jettison any other feature to support it, is to support more than one
RDBMS back-end, don't even try. Examples of software which needs to
support multiple RDBMS back-ends include, and are pretty much limited
to, ERD generators and migration tools.

> > and very likely an OLAP version. :)
>
> What's "an OLAP version" of WITH d AS...

OLAP includes clauses like WINDOW() and OVER(), but since it's not
committed yet, I don't want to get too far into it :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate