Обсуждение: Quick SQL question . . .

Can anyone tell me what is the easiest way for me to tell if a column is
unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
statement, but I can't seem to get the query to work:

SELECT DISTINCT ON (identno) count(identno) FROM some_table;

I was trying to figure out if the # of unique entries for a particular
column is equal to the # of total entries for that column.

Any suggestions?

Peter

Perhaps a little on the hacky side but....

select count(1) from some_table group by identno order by 1 desc limit 1;

If the result is anything other than 1, it's not unique.

Sorry for the hacky nature, this is the "thought about it for 10
seconds" version.  :-)

-Fran

Peter E. Chen wrote:

>Can anyone tell me what is the easiest way for me to tell if a column is
>unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
>statement, but I can't seem to get the query to work:
>
>SELECT DISTINCT ON (identno) count(identno) FROM some_table;
>
>I was trying to figure out if the # of unique entries for a particular
>column is equal to the # of total entries for that column.
>
>Any suggestions?
>
>Peter
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>

Try

select identno, count(identno) from some_table group by identno having
count(identno) >=1;


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Peter E. Chen
Sent: Thursday, May 09, 2002 2:39 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Quick SQL question . . .


Can anyone tell me what is the easiest way for me to tell if a column is
unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
statement, but I can't seem to get the query to work:

SELECT DISTINCT ON (identno) count(identno) FROM some_table;

I was trying to figure out if the # of unique entries for a particular
column is equal to the # of total entries for that column.

Any suggestions?

Peter




---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Thu, 9 May 2002, Peter E. Chen wrote:

 > Can anyone tell me what is the easiest way for me to tell if a column is
 > unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
 > statement, but I can't seem to get the query to work:
 >
 > SELECT DISTINCT ON (identno) count(identno) FROM some_table;
 >
 > I was trying to figure out if the # of unique entries for a particular
 > column is equal to the # of total entries for that column.
 >
 > Any suggestions?

SELECT identno, count(*) FROM some_table
  GROUP BY 1 HAVING COUNT(*) = 1;

Regards Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                                  http://www.thinx.ch
ThinX networked business services    Adlergasse 5, CH-4500 Solothurn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Is it just me or do the suggestion made look wrong to anyone else?

Anyway, this is the closest to my mind and I think what I think is the mistake
is just a typo. So I would use.

SELECT identno, count(identno)
   FROM some_table
   GROUP BY identno
   HAVING count(identno) > 1

or even

SELECT count(1) FROM (
   SELECT count(identno)
      FROM some_table
      GROUP BY identno
      HAVING count(identno) > 1) a

either one of which will return one of more rows if Peter's uniqueness test
fails.

Right, now someone can correct me :)


On Thu, 9 May 2002, Marie G. Tuite wrote:
> Try
>
> select identno, count(identno) from some_table group by identno having
> count(identno) >=1;
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Peter E. Chen
> Sent: Thursday, May 09, 2002 2:39 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Quick SQL question . . .
>
>
> Can anyone tell me what is the easiest way for me to tell if a column is
> unique or not?  I tried using DISTINCT ON and COUNT together in a SELECT
> statement, but I can't seem to get the query to work:
>
> SELECT DISTINCT ON (identno) count(identno) FROM some_table;
>
> I was trying to figure out if the # of unique entries for a particular
> column is equal to the # of total entries for that column.
>
> Any suggestions?
>
> Peter
>

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants