Обсуждение: Postgres Updating only changed columns against entire row

Поиск
Список
Период
Сортировка

Postgres Updating only changed columns against entire row

От
Debraj Manna
Дата:

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

Re: Postgres Updating only changed columns against entire row

От
Ron
Дата:
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.

Re: Postgres Updating only changed columns against entire row

От
"David G. Johnston"
Дата:
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.