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 1lwQji-0004oF-Bh for pgsql-admin@arkaria.postgresql.org; Thu, 24 Jun 2021 14:53:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lwQjh-0005Yz-9v for pgsql-admin@arkaria.postgresql.org; Thu, 24 Jun 2021 14:53:45 +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 1lwQjg-0005Yp-RJ for pgsql-admin@lists.postgresql.org; Thu, 24 Jun 2021 14:53:45 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lwQje-0006bD-NT for pgsql-admin@lists.postgresql.org; Thu, 24 Jun 2021 14:53:44 +0000 Received: by mail-ed1-x52e.google.com with SMTP id s15so8900765edt.13 for ; Thu, 24 Jun 2021 07:53:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=VGI/Fbn8Xx2EE1z+gjtg0m4RmWZzV51fIV4aFfBnCdY=; b=vgGwLT1Dw0mp/PDSWqhXpLe+baeHV1EaV/jO7NeTQ6Mww+D7ySwOPg4Q4zJHtDm+3n rLFymOe8yvuYovXZ292zmo1wqF9Y5AYrad4hqHG2tBmOXzZ5A89rjCsUagqSHdatukrM XwOtRTQJgFq+fs/7XBOHxj78uprCkp+vZ52keGYhhl2clLKf+SA8HNY/TGfsOAy9vAiA dyWbLC/iXJrBitH00/E6lz1LtRX8cNgqTmO4s+YhLXEIV45/zsP3gKt3S7s650oNBvUe eC7C6PAPCVsg4U52jzssKlfFf2t/SgXLoeYuyaxbSP1wH1pphhpZMl9UdSwbezG6HfVS equA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=VGI/Fbn8Xx2EE1z+gjtg0m4RmWZzV51fIV4aFfBnCdY=; b=ja6mqs+LCcXyqRDlqNWj4q8yi184WQ9DUUl2eAV7f3edMmIzATLdw9FIxCm+o3n4SU JpepyuQkxldFFZqrP8yqCbs81cAGWK2EyVgD0MwHlXsbss+JYS7LGue+BF1HmBmh+R12 do6Kea97OZa6n2WSmL7JVBrYxmZtuq0wGg8SlYMhvCDFw9WQblFXasgLZBMTbY/+en0H pIJk95GNs/h7bUkeKnr/yPELcaT6soPQYSPBYNOdPOLdUJ8ObyT5lytumvYl9gHFi6PJ bW1G5fd5vi4RxtQkYu7FSMmYIb8DBqodfuahfMkq2VOISbrY3cp2f5GmjvmabAsogv2X 6eHg== X-Gm-Message-State: AOAM533wJdsf9RY/uIZoJCOQGIopYsszc+6EaZXB03JZs2rQ1lMIhvbg J7KI/QmTusRXyrOq/GQ5LTvjkyQVaNg= X-Google-Smtp-Source: ABdhPJx6w9WQ67M2nhw2U4NqDoX1LMViWc+4xD6XNdH5tQoNGac8fHkGrxws3I5KDhpK2sGeBTBxkg== X-Received: by 2002:a05:6402:10c3:: with SMTP id p3mr4119018edu.175.1624546421132; Thu, 24 Jun 2021 07:53:41 -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 z27sm1345291ejl.113.2021.06.24.07.53.40 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 24 Jun 2021 07:53:40 -0700 (PDT) Subject: Re: Issue with delete To: Guillaume Lelarge Cc: "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> <34da09ed-97a9-557d-6d92-54c22fc5bc82@gmail.com> From: "Radoulov, Dimitre" Message-ID: <7e32529f-5692-1838-8468-4b8deff424ea@gmail.com> Date: Thu, 24 Jun 2021 16:55:13 +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: Content-Type: multipart/alternative; boundary="------------B2E17A3B6BCAA6C6C7A7ADA0" 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. --------------B2E17A3B6BCAA6C6C7A7ADA0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 24/06/2021 16.48, Guillaume Lelarge wrote: > Le jeu. 24 juin 2021 à 16:02, Radoulov, Dimitre > a écrit : > > >> 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? > > > > That syntax doesn't exist. I should have tried the statement before asking, thanks! I got confused by other databases (used it on Db2). Best regards Dimitre --------------B2E17A3B6BCAA6C6C7A7ADA0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 24/06/2021 16.48, Guillaume Lelarge wrote:
Le jeu. 24 juin 2021 à 16:02, Radoulov, Dimitre <cichomitiko@gmail.com> a écrit :


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?



That syntax doesn't exist.


I should have tried the statement before asking, thanks!
I got confused by other databases (used it on Db2).


Best regards
Dimitre

--------------B2E17A3B6BCAA6C6C7A7ADA0--