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 1oHJYU-00021o-Ea for pgsql-admin@arkaria.postgresql.org; Fri, 29 Jul 2022 06:33:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oHJYR-0007Lj-Qd for pgsql-admin@arkaria.postgresql.org; Fri, 29 Jul 2022 06:32:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oHJYR-0007La-5G for pgsql-admin@lists.postgresql.org; Fri, 29 Jul 2022 06:32:59 +0000 Received: from mail-oa1-x2a.google.com ([2001:4860:4864:20::2a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oHJYO-0003eH-A0 for pgsql-admin@postgresql.org; Fri, 29 Jul 2022 06:32:58 +0000 Received: by mail-oa1-x2a.google.com with SMTP id 586e51a60fabf-10e615a36b0so3439067fac.1 for ; Thu, 28 Jul 2022 23:32:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=3uDiwjNi5yDn5IX8CalwLkT041g495QHHqsaasTuckY=; b=a3bICAgLaR+tWcJhSezIKk4Gzbm1hNrIrT5b36mFYocDbEQkS8ZhMJAer7mMCx6V3s ITN52C5oF6sKsKCg8QKolcLQITKaDyize1oGUXGZ2TH/yHo0z84NGyshz49oASDoKCet 0BJj4Yi7pr+o4J/U/6e/LtmUmBIr8iX/Pit+cBSQgGaDwdvskhDStNkoIZ6rbJKKOoKl JDwJsOzm18IQQ2dhBE8jC/6oWFzGx+FpOLVb2fvNmApXUvd2EE5S3sHfUuRp30pCe8BQ 8sg+0QcjDh2S56WExJmjfJLtwuBds/0F34sR0HQjTbgitk1Tq46TI/N2nuwisrrTouhH uPHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=3uDiwjNi5yDn5IX8CalwLkT041g495QHHqsaasTuckY=; b=1WNcVBrbnsXaOErEJtGs5up5LIvwuBF+XGOuUUEyBgGsJysyylIbvZcutRujsFi666 oIMD31D3OUD276L8JXWn3H+nWqpegsRupMc6hdyI3EXNi1JjO/4c3ZBizLzkAPdYHGHX fFFh82z9WsVqMlrqB+6RiWlTGZH9yNv3Be4FTLHvixbOyq86PQAZdM/XjrlJJlWDw9RM 9oO/dW0GKH13m4Zbu2rC+zh149YrId/iZpyiuXw+9+GOscUsRHKNxAFjMJo1p1DIeDj4 hc/ZWn6VE+LI1GtXOKKFBM2mzVIj4GIigT0h19XVtRumNTQWLESsHE7mZKsIaDoPNZ9/ 2OLQ== X-Gm-Message-State: AJIora9KST1SOE91aElfHA5rmuR6F4e/EZR5+Fy+LyD/U6BHSto6aK67 1ZKFUr+At0U7mksFtxG21+fKwVrEtrI= X-Google-Smtp-Source: AGRyM1tovhmaLUpGetgFBI+aVJWcfaQcDWvMWtvgwOiTrV6egMua62JSAL3Gq7dr4Ac/QQccR4qOAg== X-Received: by 2002:a05:6870:434e:b0:10b:8204:3ec4 with SMTP id x14-20020a056870434e00b0010b82043ec4mr1473175oah.113.1659076375187; Thu, 28 Jul 2022 23:32:55 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id br24-20020a056830391800b0061c74b9ac26sm1158466otb.57.2022.07.28.23.32.54 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 28 Jul 2022 23:32:54 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------zbXWzEQquxR0jjtTWRAGWw2W" Message-ID: <88ffa330-e0e2-f9da-2469-97dbeca66906@gmail.com> Date: Fri, 29 Jul 2022 01:32:53 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.9.1 Subject: Re: Postgres Updating only changed columns against entire row Content-Language: en-US To: pgsql-admin References: From: Ron In-Reply-To: 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. --------------zbXWzEQquxR0jjtTWRAGWw2W Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 7/29/22 01:19, Debraj Manna wrote: > > Let's say I have a table like below > > |CREATE TABLE empl( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, address > VARCHAR NOT NULL, status int NOT NULL, metadata varchar NOT NULL ); | > > and it has data like > > |(2, 'tuk', 'ind', 1, 'meta') ... (200, 'tuka', 'eng', 2, 'meta2') | > > 1. |update empl set status = 2 where (id = 2);| > 2. |update empl set status = 2, name='tuk', address='ind', > metadata='meta' where (id = 2);| > > Can someone let me know if there are any advantages of doing #1 over #2 > assuming there are no other indices or triggers on the table? Which is > generally preferred? > I'll counter with my own question: why would you go through all the extra coding effort (which leads to *more places that you can make a mistake*, like accidentally typing |metadata='meeta'|)? Also, the parentheses are superfluous in this case. -- Angular momentum makes the world go 'round. --------------zbXWzEQquxR0jjtTWRAGWw2W Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On 7/29/22 01:19, Debraj Manna wrote:

Let's say I have a table like below

CREATE TABL=
E empl(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL,
   address VARCHAR NOT NULL,
   status int NOT =
NULL,
   metadata varchar NOT NULL
);

and it has data like

(2, 'tuk', 'ind', 1, '=
meta')
=2E..
(200, 'tuka', 'eng=
', 2, 'meta2')
  1. update empl set status =3D 2 where (id =3D 2);
  2. update empl set status =3D = 2, name=3D'tuk', address=3D'ind', metadata=3D'meta' where (id =3D 2);

Can someone let me know if there are any advantages of doing #1 over #2 assuming there are no other indices or triggers on the table? Which is generally preferred?


I'll counter with my own question: why would you go through all the extra coding effort (which leads to more places that you can make a mistake, like accidentally typing metadata=3D'meeta')?

Also, the parentheses are superfluous in this case.

--
Angular momentum makes the world go 'round.
--------------zbXWzEQquxR0jjtTWRAGWw2W--