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.

Answer 2

It's either a typo or someone misunderstood what they were writing.

I don't know all the details, but you can use parentheses as precedence operators (just like in math). However, I think it ends up that you can put parentheses around a lot of things without actually changing their meanings.

For example, the following 2 queries return exactly the same thing:

select foo
from bar

select (foo)
from bar

It's confusing because the you can also use parentheses to group columns into records, for example:

select (foo, baz)
from bar

So in your original query, what they've actually written would be equivalent to this:

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

which may or may not be what they intended. If I had to guess I would guess they were going for the "DISTINCT ON(...)" syntax mentioned in some of the other answers.