Обсуждение: How to find not unique rows in a table?
Assuming you have a table where some rows have the same values in all columnes, how do you find these rows?
am Wed, dem 08.10.2008, um 13:20:47 +0200 mailte A B folgendes: > Assuming you have a table where some rows have the same values in all > columnes, how do you find these rows? You can use the ctid-column: test=*# select * from dup; a | b ---+--- 1 | 1 1 | 2 2 | 1 1 | 1 3 | 3 3 | 3 (6 rows) test=*# select * from dup where (ctid,a,b) not in (select distinct on (a,b) ctid,* from dup); a | b ---+--- 1 | 1 3 | 3 (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Wed, Oct 08, 2008 at 01:36:37PM +0200, A. Kretschmer wrote: > test=*# select * from dup; > a | b > ---+--- > 1 | 1 > 1 | 2 > 2 | 1 > 1 | 1 > 3 | 3 > 3 | 3 > (6 rows) > > test=*# select * from dup where (ctid,a,b) not in (select distinct on (a,b) ctid,* from dup); > a | b > ---+--- > 1 | 1 > 3 | 3 > (2 rows) another approach: select a,b from dup group by a,b having count(*) > 1; depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
A B wrote: > Assuming you have a table where some rows have the same values in all > columnes, how do you find these rows? There is no reliable, time-independent way. That's one reason why you have primary keys on tables. There is the system column "ctid", but that may change any second. Yours, Laurenz Albe