Thread: Postgres Updating only changed columns against entire row
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')
update empl set status = 2 where (id = 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
On 7/29/22 01:19, Debraj Manna wrote:
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
Also, the parentheses are superfluous in this case.
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')
update empl set status = 2 where (id = 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.
Angular momentum makes the world go 'round.
On Thursday, July 28, 2022, Debraj Manna <subharaj.manna@gmail.com> wrote:
update empl set status = 2 where (id = 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.