Skip site navigation (1) Skip section navigation (2)

DUPS in tables columns ERROR: column ". . . " does not exist

From: "Albretch Mueller" <lbrtchx(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: DUPS in tables columns ERROR: column ". . . " does not exist
Date: 2008-08-30 11:47:28
Message-ID: 9ef66fac0808300447p4d57af1ch39ca5c8bed360a7f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
 Hi,
~
 I am trying to get dups from some data from files which md5sums I
previously calculated
~
 Here is my mere mortal SQL
~
SELECT md5, COUNT(md5) AS md5cnt
FROM jdk1_6_0_07_txtfls_md5
WHERE (md5cnt > 1)
GROUP BY md5
ORDER BY md5cnt DESC;
~
 and this is what I get:
~
jpk=# SELECT md5, COUNT(md5) AS md5cnt
FROM jdk1_6_0_07_txtfls_md5
WHERE (md5cnt > 1)
GROUP BY md5
ORDER BY md5cnt DESC;
jpk-# jpk-# jpk-# jpk-# ERROR:  column "md5cnt" does not exist
LINE 3: WHERE (md5cnt > 1)
~
 I think I know what that one means based on the clear error message,
namely md5cntis not a table column itself, but I still think there
should be a way to formulate a simple query like this because PG does
take "ORDER BY md5cnt DESC" even if md5cnt is not a table column, why
on earth then it does not swallow and digest the "WHERE (md5cnt > 1)"
part?
~
 You could go the monkey way running a query like:
~
 SELECT md5, COUNT(md5) AS md5cnt FROM jdk1_6_0_07_txtfls_md5 GROUP BY
md5 ORDER BY md5cnt DESC;
~
 and then use code to jump of the loop when md5cnt becomes 1 or you
could use nested SQL statements
~
 How can you find duplicate records in a table?
~
 Thanks
 lbrtchx

Responses

pgsql-general by date

Next:From: Albretch MuellerDate: 2008-08-30 12:23:25
Subject: Re: ERROR: relation . . . does not exist
Previous:From: Fernando MorenoDate: 2008-08-30 04:01:11
Subject: Re: RAISE NOTICE format in pgAdmin

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group