Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lwPvX-0003Gm-PM for pgsql-admin@arkaria.postgresql.org; Thu, 24 Jun 2021 14:01:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lwPvW-00014V-MO for pgsql-admin@arkaria.postgresql.org; Thu, 24 Jun 2021 14:01:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lwPvW-00010n-Av for pgsql-admin@lists.postgresql.org; Thu, 24 Jun 2021 14:01:54 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lwPvU-000655-4R for pgsql-admin@lists.postgresql.org; Thu, 24 Jun 2021 14:01:54 +0000 Received: by mail-ed1-x530.google.com with SMTP id c7so8687847edn.6 for ; Thu, 24 Jun 2021 07:01:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=sn2rW1rZ6Lb+g3QB/4yOz5Gg4cKrv4PJTCkxQKBamaw=; b=py4zu07AGQBmj/zBJMijmxLfR4AVu+Estqfz00YRcTBoYCZg3+xIs8F+Ais2yKko6q MrMjVepCMQNGeXj0x7ZDLfFrW1freLUVEnBLRhkDd1xiKrzjVJlvcMB1FzKl9LLGBbin 07uzwFZ9+y8/I/bMAEzkNdcvC4/UM7f9848r1ShU4s5x5DeaagR1Zmv1CnsX3a5cdYwd SUimq2w2ErLbaqQAS+B8LLt6n+k4oZyrKJErXIquexbipM5+xTJM81mYQ3XJfQnfpVU3 aQleWnvj9eUMB80sEHobbN8+PTNx4Y2F361hB9cB+/3/fDnGcz7czIc0y1T2a2GhJhQv 2Xxw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=sn2rW1rZ6Lb+g3QB/4yOz5Gg4cKrv4PJTCkxQKBamaw=; b=byA1QLCscGHO1vcyOMSgNAZkBBfEtaXUI8Y+BjcSoUlWA05mEZKkRgCffI/Fma8Oex uOb/Z0+94zE2vC4KtBE14LElhmV+rbHfbj/IeCEP6yvpEFixVF8Lv1tdmAZGpwHHlSJm UcRJwER48yXkWp0Y7tRt+S/r5hwVl7M3c06G63fiVlKoYwOEKdlrVrwacphyHHF6bC67 qKpOUcUDWiCli9PoCyLXrAuM+pe+/Watz63+keYj2gD5l96oJOKa2pHHsqzhgXppPppQ 7DZd/wQSkMRsEal5CzreljfLhBvPKtOQetCXUVdG10gYiTzyKU+HfpRr5T1A2jMABf6c wIJw== X-Gm-Message-State: AOAM530rvuNNnpzzIf5pSF3SWrssOQMrwrdsgoija9HZVfOT1xZZqfvD orO7dbD2BPw794RSKDLUKm789K565Gw= X-Google-Smtp-Source: ABdhPJz3ZQs6RIl5lvp9/jjkd/X3eWzw9Wh+hUVTgRzX9UxQNMWJWvTssKq5Xl0y+Jbqg2PgftCIYQ== X-Received: by 2002:aa7:dccb:: with SMTP id w11mr7150610edu.96.1624543310527; Thu, 24 Jun 2021 07:01:50 -0700 (PDT) Received: from [192.168.1.8] (net-93-66-70-148.cust.vodafonedsl.it. [93.66.70.148]) by smtp.gmail.com with ESMTPSA id hg25sm1240071ejc.51.2021.06.24.07.01.49 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 24 Jun 2021 07:01:49 -0700 (PDT) Subject: Re: Issue with delete To: "pgsql-admin@lists.postgresql.org" References: <266147493.2758919.1624525689006.ref@mail.yahoo.com> <266147493.2758919.1624525689006@mail.yahoo.com> <651524086.2768711.1624527250102@mail.yahoo.com> From: "Radoulov, Dimitre" Message-ID: <34da09ed-97a9-557d-6d92-54c22fc5bc82@gmail.com> Date: Thu, 24 Jun 2021 16:03:22 +0200 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101 Thunderbird/78.11.0 MIME-Version: 1.0 In-Reply-To: <651524086.2768711.1624527250102@mail.yahoo.com> Content-Type: multipart/alternative; boundary="------------AE90E7A885EDBCF5D091D4DB" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------AE90E7A885EDBCF5D091D4DB Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit > Am 24.06.21 um 11:08 schrieb Rohit Rajput: > Good day everyone, > > I have few tables where i need to delete above 50 million rows each. > Since i dont want to delete them all in one shot and block my table > for extended time, i am trying to use limit with delete but its giving > me error every time. May i please get some assistance here? Thanks > > Here is what i am running: > > delete from mytable where id in (select id from mytable where > condition=true limit 1000); > Wouldn't "delete from mytable where condition=true limit 1000" be sufficient? Regards Dimitre --------------AE90E7A885EDBCF5D091D4DB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit


Am 24.06.21 um 11:08 schrieb Rohit Rajput:
Good day everyone,

I have few tables where i need to delete above 50 million rows each. Since i dont want to delete them all in one shot and block my table for extended time, i am trying to use limit with delete but its giving me error every time. May i please get some assistance here? Thanks

Here is what i am running:

delete from mytable where id in (select id from mytable where condition=true limit 1000);


Wouldn't "delete from mytable where condition=true limit 1000" be sufficient?


Regards
Dimitre

--------------AE90E7A885EDBCF5D091D4DB--