distinct() function (not select qualifier) in postgres

Question

I just came across a SQL query, specifically against a Postgres database, that uses a function named "distinct". Namely:

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

Note this is NOT the ordinary DISTINCT qualifier on a SELECT -- at least it's not the normal syntax for the DISTINCT qualifier, note the parentheses. It is apparently using DISTINCT as a function, or maybe this is some special syntax.

Any idea what this means?

I tried playing with it a little and if I write

select distinct(foo)
from bar

I get the same results as

select distinct foo
from bar

When I combine it with other fields in the same select, it's not clear to me exactly what it's doing.

I can't find anything in the Postgres documentation.

Thanks for any help!

Answer 1

^{(The question is old, but comes high in Google results for “sql distinct is not a function” (second, first of Stack Overflow) and yet is still missing a satisfying answer, so...)}

Actually this is the ordinary DISTINCT qualifier on a SELECT -- but with a misleading syntax (you are right about that point).

DISTINCT is never a function, always a keyword. Here it is used (wrongly) as if it were a function, but

select distinct(pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

is in fact equivalent to all the following forms:

_{-- add a space after distinct:}

select distinct (pattern) as pattern, style, ... etc ...
from styleview
where ... etc ...

_{-- remove parentheses around column name:}

select distinct pattern as pattern, style, ... etc ...
from styleview
where ... etc ...

_{-- indent clauses contents:}

select distinct
    pattern as pattern, style, ... etc ...
from
    styleview
where
    ... etc ...

_{-- remove redundant alias identical to column name:}

select distinct
    pattern, style, ... etc ...
from
    styleview
where
    ... etc ...

Complementary reading:

• http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx
• https://stackoverflow.com/a/1164529

---

Note: OMG Ponies in an answer to the present question mentioned the DISTINCT ON extension featured by PostgreSQL.
But (as Jay rightly remarked in a comment) it is not what is used here, because the query (and the results) would have been different, e.g.:

select distinct on(pattern) pattern, style, ... etc ...
from styleview
where ... etc ...
order by pattern, ... etc ...

equivalent to:

select  distinct on (pattern)
    pattern, style, ... etc ...
from
    styleview
where
    ... etc ...
order by
    pattern, ... etc ...

Complementary reading:

• http://www.noelherrick.com/blog/postgres-distinct-on

---

Note: Lukas Eder in an answer to the present question mentioned the syntax of using the DISTINCT keyword inside an aggregate function:
the COUNT(DISTINCT (foo, bar, ...)) syntax featured by HSQLDB
(or COUNT(DISTINCT foo, bar, ...) which works for MySQL too, but also for PostgreSQL, SQL Server, Oracle, and maybe others).
But (clearly enough) it is not what is used here.