Re: WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation - Mailing list pgsql-general

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
>>> David Fetter <david@fetter.org> wrote:
>>>> 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.

> So what would be the advantage compared to subselect?

None, David just has WITH on the brain ;-)

The subselect syntax certainly seems like the one most likely to work
across different SQL implementations.  WITH is a pretty recent addition
to the SQL spec, and DISTINCT with multiple aggregate arguments isn't
in the spec at all.  The COUNT(DISTINCT ROW(x,y)) hack is a cute idea
but I'm dubious that that's portable either (it certainly doesn't work
in pre-8.4 PG).

            regards, tom lane