Re: More on unique vs distinct

On Nov 21, 2006, at 5:27 , Wm.A.Stafford wrote:

> I guess I should have been a bit more specific.  Here is an example
> of the type of query I'm finding in the Oracle app and the results
> of my attempt to find  a PostgreSQL equivalent.
>
> select count(unique 'source_id') from obis_sources;
> pgAdmin III query responds:
> ERROR:  syntax error at or near "'source_id'" at character 23
>
> select count(distinct 'source_id') from obis_sources;
> pgAdmin III query responds:
> ERROR:  could not identify an equality operator for type "unknown"

I think the single quotes are the cause of your problem. Single-
quotes indicate a string literal. In the first case, you can see that
the syntax error is putting double quotes around 'source_id',
including the single quotes, because it's expecting an identifier
(not a string literal). In the second case it's seeing a string
literal but it's not sure what type to implicitly cast it to.

In either case, I think what you're looking for is:

select count(distinct source_id) from obis_sources;

Michael Glaesemann
grzm seespotcode net