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 1kkxBa-0007GS-Bl for pgsql-performance@arkaria.postgresql.org; Thu, 03 Dec 2020 22:34:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kkxBY-0002Ph-9W for pgsql-performance@arkaria.postgresql.org; Thu, 03 Dec 2020 22:34:48 +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 1kkxBX-0002PV-Tq for pgsql-performance@lists.postgresql.org; Thu, 03 Dec 2020 22:34:48 +0000 Received: from mail-io1-xd2c.google.com ([2607:f8b0:4864:20::d2c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kkxBQ-00032D-ML for pgsql-performance@lists.postgresql.org; Thu, 03 Dec 2020 22:34:47 +0000 Received: by mail-io1-xd2c.google.com with SMTP id r9so3800626ioo.7 for ; Thu, 03 Dec 2020 14:34:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:content-transfer-encoding:in-reply-to :user-agent; bh=p84O8q23dC/Ty6pbWWlfASe0v+inZNo2+n8NlVO6QPw=; b=NxKq3yYpGWDO57OFDHUtQ11xK8KxcxKgOnl1VaKoSRjfJ7t0lauF0sBD+IBF9jfNG9 DQ1txHft12KVVlBZliUEGSTfarJkJq5XTF5+XXTCZ5BnzNJ/hKjPEIQztuoZLWth/Wj5 g/a7hyd9QUnlunkZPGvuO+MsFYAZp+66lytQOqQKMd0eAcodRRuxNcDIFEI6TUGfu6MJ Bt1D5r1p6ORGbOJRa8NAY2KImoNZEk6AymwSVBLsqANqf1bJnUOFxNzgkB8ewB6M2z7g p0Vn2Ner5je5CFYruagxBukyYA32COEPn9uxqMTIO2kA4NPmh/nEd+GvtT/SsO770UA3 wQLw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:content-transfer-encoding :in-reply-to:user-agent; bh=p84O8q23dC/Ty6pbWWlfASe0v+inZNo2+n8NlVO6QPw=; b=PDWgq1w/0pTFLVNA1soBNPxAiWLeLrmE7MN10lSd8LKJg+TcDNM4nKzlU5vmnncIyo opWw62WsngwxrDYtocPzwSE1x2OtUsh//GSTJutnMONBiTiAzPwAFDKWBkqpb2SL7N+Y s9zGF16A06hFGRWTXivWY8lmWLJ5CCUa4c6tsh4dy461y4DGnSf9Q3nXBVpdxqhIShqi GjX3KPhOX38c4XfRO8w4Egs8v/c57U5ACEw6UJ1+voXIS/M/1/Mf41xsO1gkrq8X5gbb lc4OAdlo4tXxaaVJMs50vfSUt7p7ZuR8uC6KF9QyCMBzxu84/FKi+65gw6BIb1+uZvEP 5Cnw== X-Gm-Message-State: AOAM531nmrM9D53/VjssxbXuyeevz7WksaUFHWH5o96LQjQlmMI7sush BDHITqHINDw2CGkCqUw/YMUGamc2EsZTtmeF X-Google-Smtp-Source: ABdhPJwmuV3v5BfwW7NL2AEwZ7CmEoTD8csLN4EbRHiTRlRCw0MuzJHFg6zqt52wRxjFREV8aemu7w== X-Received: by 2002:a5e:da46:: with SMTP id o6mr1882700iop.139.1607034878290; Thu, 03 Dec 2020 14:34:38 -0800 (PST) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id o10sm366922ili.82.2020.12.03.14.34.37 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 03 Dec 2020 14:34:37 -0800 (PST) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 412728006C6; Thu, 3 Dec 2020 16:34:36 -0600 (CST) Date: Thu, 3 Dec 2020 16:34:36 -0600 From: Justin Pryzby To: Nagaraj Raj Cc: pgsql-performance@lists.postgresql.org Subject: Re: Temporarily disable not null constraints Message-ID: <20201203223436.GT24052@telsasoft.com> References: <815507272.4135293.1607025495638.ref@mail.yahoo.com> <815507272.4135293.1607025495638@mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <815507272.4135293.1607025495638@mail.yahoo.com> User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Thu, Dec 03, 2020 at 07:58:15PM +0000, Nagaraj Raj wrote: > Can we disable not null constraints temporarily in the session-based transaction, like we disable FK constraints?  If you're trying to temporarily violate the not-null constraint.. I don't know if it's a good idea.. ..but maybe this feature in v12 helps you: https://www.postgresql.org/docs/12/sql-altertable.html | Ordinarily this is checked during the ALTER TABLE by scanning the entire table; however, if a valid CHECK constraint is found which proves no NULL can exist, then the table scan is skipped. When you're done violating constraints, you can ALTER .. ADD CONSTRAINT .. CHECK (.. IS NOT NULL) NOT VALID, and then ALTER .. VALIDATE CONSTRAINT, and then ALTER column SET NOT NULL. -- Justin