Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qstBo-009w9r-NU for pgsql-general@arkaria.postgresql.org; Tue, 17 Oct 2023 23:09:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qstBm-00FKkw-Ky for pgsql-general@arkaria.postgresql.org; Tue, 17 Oct 2023 23:09:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qstBm-00FKje-5j for pgsql-general@lists.postgresql.org; Tue, 17 Oct 2023 23:09:26 +0000 Received: from mail-pj1-x1034.google.com ([2607:f8b0:4864:20::1034]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qstBj-000zgk-TZ for pgsql-general@lists.postgresql.org; Tue, 17 Oct 2023 23:09:25 +0000 Received: by mail-pj1-x1034.google.com with SMTP id 98e67ed59e1d1-27d1b48e20dso3671522a91.0 for ; Tue, 17 Oct 2023 16:09:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=illuminatedcomputing-com.20230601.gappssmtp.com; s=20230601; t=1697584162; x=1698188962; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=1TNiIfboXTXcoQpYCWVI0t+T6jGaeJpdTy3j48pkBnk=; b=YBc69j0DgrlOoootEX7s4PkDuFX9aLiU9B5SHKZsEXCtfxv4VpWcVIup53IfB/4E5j JLKN5XyvRb35g85dON6GbTiYj0anlcKrQPwNEvS8XelTYf2hcNk4RlxAZCeq/C8db3fi AqGx4J17CkyaZlIcoTVkQtOnODwpwU2/SJmgiuAETXp3/hV15eCOaHqsXVZOFGIKFlNR OGEwahll4l/DIYh88iY+mISwrbnbtkL1oQSpE/ugAajvjts26Rm/7/wWBaeifGq2a4Ta +zGbwnh80uCl/g5RE6Lj7fVRQBF84fp50Fnbz+HvHrADX5n/7nQJsfmR1Ui0ubU9xpLb 5oEg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697584162; x=1698188962; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=1TNiIfboXTXcoQpYCWVI0t+T6jGaeJpdTy3j48pkBnk=; b=MiLS528BGHaQg7ww2T2iIXloPW+eA1u/NaPoDTV+0uosHNDNJnk7MqB/iUJbBao9sp Z2kwLxHjrLK4h1mlGm04/gkbcSm3CU/uyNfJKPVeOEMuccy8mgdQfX1wynbpDUsBPMuv mUhvRGIyz+rYh5NE84hYr+STBRIkWKL6GPn5nOhoJLVSsNW/SV6F/VMs3dhjyjLlK5/T jZr/03eLELQImwZo8jw63yVcEbvwKSgsb52lh8fnBAeuNLxD3OGpfSYdwJKvW4rmJuyU 8stsl4MkO3NqFkeGDWISgcnvrLlfKhRNkInBXag/X8BAFPE/fGF0PAR7NaRXLPanieLJ 4zNQ== X-Gm-Message-State: AOJu0YwO1Mk+MK6rWV7uG7HVuEWzJA165WzVAX4v1vNDz6NSaB4vYK1E O0V04a1TEYs1mYDVExJDb3IMso17mt+Kk2qSN/I= X-Google-Smtp-Source: AGHT+IHgx7PN/b/U1c0OCnc2/zg9CRmJ/wlvVKgbFIri3PhdIuvaURFBvWYlc7v77zStl3eqXxHikw== X-Received: by 2002:a17:90a:1e:b0:27c:f4c7:57f with SMTP id 30-20020a17090a001e00b0027cf4c7057fmr3110440pja.35.1697584162341; Tue, 17 Oct 2023 16:09:22 -0700 (PDT) Received: from [192.168.2.139] ([50.53.98.191]) by smtp.googlemail.com with ESMTPSA id i10-20020a17090a2a0a00b0027cf8869ee7sm863032pjd.0.2023.10.17.16.09.18 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 17 Oct 2023 16:09:18 -0700 (PDT) Message-ID: Date: Tue, 17 Oct 2023 16:09:17 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.15.1 Subject: Re: Need efficient way to remove (almost) duplicate rows from a table Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Paul Jungwirth In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 10/17/23 15:48, David Gauthier wrote: > I have a table with millions of records and there are a lot of "almost" > duplicates that I want to get rid of in an efficient way.  Best to > illustrate with a simple example... This sounds like a classic use-case for a "correlated subquery". Assuming you want to keep the lowest twig_id, you could do this: ``` paul=# select * from twigs; limb_id | branch_id | twig_id | surfarea ---------+-----------+---------+---------- 1 | 1 | 1 | 100 1 | 1 | 2 | 103.7 1 | 1 | 3 | 103.7 1 | 1 | 4 | 110.4 1 | 2 | 1 | 120 1 | 2 | 2 | 123.6 1 | 2 | 3 | 128.1 1 | 2 | 4 | 128.1 1 | 2 | 5 | 128.1 2 | 1 | 1 | 100 2 | 1 | 3 | 104.4 2 | 1 | 4 | 131.9 (12 rows) Time: 0.218 ms paul=# delete from twigs as t1 where exists (select 1 from twigs as t2 where (t1.limb_id, t1.branch_id, t1.surfarea) = (t2.limb_id, t2.branch_id, t2.surfarea) and t1.twig_id > t2.twig_id); DELETE 3 Time: 8.555 ms paul=# select * from twigs; limb_id | branch_id | twig_id | surfarea ---------+-----------+---------+---------- 1 | 1 | 1 | 100 1 | 1 | 2 | 103.7 1 | 1 | 4 | 110.4 1 | 2 | 1 | 120 1 | 2 | 2 | 123.6 1 | 2 | 3 | 128.1 2 | 1 | 1 | 100 2 | 1 | 3 | 104.4 2 | 1 | 4 | 131.9 (9 rows) Time: 0.231 ms ``` That says "delete from t1 where there is a matching t2 with a lower twig_id." If you want to keep the highest-numbered twig_id, the sql is easy to adjust. Regards, -- Paul ~{:-) pj@illuminatedcomputing.com