Postgres Pro
Downloads
Home   >   mailing lists

Thread: More on unique vs distinct

More on unique vs distinct

From
"Wm.A.Stafford"
Date:
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"

Thanks for all the help,
-=bill

Re: More on unique vs distinct

From
Rodrigo Gonzalez
Date:
Not 100% sure....

select count(*) from (select distinct source_id from obis_sources) t

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"
>
> Thanks for all the help,
> -=bill
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

Re: More on unique vs distinct

From
Casey Duncan
Date:
On Nov 20, 2006, at 12:27 PM, 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"

The single quotes are confusing things here, it treats 'source_id' as
a literal string instead of a column name. Try it without the quotes:

   select count(distinct source_id) from obis_sources;

hth,

  -Casey

Re: More on unique vs distinct

From
"Joshua D. Drake"
Date:
On Mon, 2006-11-20 at 19:46 -0300, Rodrigo Gonzalez wrote:
> Not 100% sure....
>
> select count(*) from (select distinct source_id from obis_sources) t

SELECT count(source_id) FROM obis_sources GROUP BY source_id ?

Joshua D. Drake


>
> 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"
> >
> > Thanks for all the help,
> > -=bill
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that your
> >       message can get through to the mailing list cleanly
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Re: More on unique vs distinct

From
Michael Glaesemann
Date:
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