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?

If you want to compare a column against null use CASE WHEN val IS NULL. Using equality won't work. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 26, 2018 at 5:45
I don't know what $1 is, but yes it would probably work. Are you using a programming language with Postgres? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 26, 2018 at 5:49
@TimBiegeleisen Yes, I'm using ReactJS on the frontend and then NodeJS + ExpressJS for the server. I'm using postgresql. — Austin Callaghan
– Austin Callaghan, Commented Sep 26, 2018 at 6:05

wildplasser · Accepted Answer · 2018-09-26 12:46:20Z

11

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`

edited Sep 26, 2018 at 12:46

answered Sep 26, 2018 at 10:34

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Austin Callaghan Over a year ago

Could you explain the last one?

wildplasser Over a year ago

if val <> $1 is false, then val and $1 must be equal. (that is the first one)

Austin Callaghan Over a year ago

I tried this and it worked update tester set val = case when val <> NULL then 'peaches' else 'peaches' end where id = 1; but then I figured I should check if the input is null like so, update tester set val = case when $1 <> NULL then $1 else val end where id = 1; Final question, if I am chaining case when statements can I throw a where clause after the last one to determine which row is being updated?

Collectives™ on Stack Overflow

Conditional updating in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related