conditional updating in postgreSQL

Question

I'm testing some conditional rendering to see if it behaves as expected on a small scale before implementing it on a larger table.

The goal is to create an update query that will only update the columns that a user has input new data in and if there is no data then nothing is done to those columns

here's my case when then test. The row with the null value was not updated 'newValue'

drop table if exists tester

create table tester (
id serial primary key
val TEXT,
)

insert into tester (val) values (null)

select * from tester

update tester 
set val = case 
when val = null 
then 'newValue' 
else val 
end;

Here's a simple example of what I want to produce ($1 is the user input)

drop table if exists tester

create table tester (
id serial primary key
val TEXT,
)

insert into tester (val) values (null)

select * from tester

update tester 
set val = case 
when val != $1
then $1 
else val end
where id = 1

what I'm expecting to happen is the insert to create a row with the id of 1 and a val cell that equals null.

Then in the update query, I'm thinking the update will check if the stored data in val on row 1 is NOT equal to the input value, if that is true then the input value will get set to val for row 1.

Is that logic and syntax correct? If not, what is incorrect? Thanks!

Answer 1

Assuming that the user input $1 will never be NULL:

---

update tester 
set val = $1
where id = 1
and val <> $1 -- avoid updating with the same value
    ;

---

Note: your:

 `case when val != $1 then $1 else val end`

can be reduced to:

 `case when val <> $1 then $1 else $1 end`

or:

 `$1`