pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres Updating only changed columns against entire row
3+ messages / 3 participants
[nested] [flat]
* Postgres Updating only changed columns against entire row
@ 2022-07-29 06:19 Debraj Manna <subharaj.manna@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Debraj Manna @ 2022-07-29 06:19 UTC (permalink / raw)
To: pgsql-admin
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?
Postgres Version - 10.17
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Postgres Updating only changed columns against entire row
@ 2022-07-29 06:32 Ron <ronljohnsonjr@gmail.com>
parent: Debraj Manna <subharaj.manna@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ron @ 2022-07-29 06:32 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Postgres Updating only changed columns against entire row
@ 2022-07-29 12:26 David G. Johnston <david.g.johnston@gmail.com>
parent: Debraj Manna <subharaj.manna@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2022-07-29 12:26 UTC (permalink / raw)
To: Debraj Manna <subharaj.manna@gmail.com>; +Cc: pgsql-admin
On Thursday, July 28, 2022, Debraj Manna <subharaj.manna@gmail.com> wrote:
>
> 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?
>
Neither…
You’d write: update empl set status = $1, name = $2, … where id = $N;
Then assign some variables in your code to $1, $2, etc…
By having a parameter for each field one query can deal with changes to any
of the data elements.
> Postgres Version - 10.17
>
That doesn’t seem relevant, this is a theory question.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-07-29 12:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-29 06:19 Postgres Updating only changed columns against entire row Debraj Manna <subharaj.manna@gmail.com>
2022-07-29 06:32 ` Ron <ronljohnsonjr@gmail.com>
2022-07-29 12:26 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox