I am new to postgres and trying to update the columns where the status is either null or new. The below query is validating for NEW but not for empty string/null value. Is there any way to achieve this?
update test_table c
set FLAG = -1, err_det='Incorrect entry'
where FName is not null and Status in ('NEW','')
This query is being used inside a function.
I think this will help you if your default value is NULL
update test_table c set FLAG = -1, err_det='Incorrect entry' where FName is not null and (Status IS NULL OR Status='NEW');
Considering change Status in ('NEW','') to (Status IS NULL OR Status='NEW')
Reading your question I was unsure if you wanted to update just NULL and the 'NEW' value or 'empty string/null value' as you mentioned.
Below will work for the values NULL/'NEW'/ and 'empty string'.
UPDATE test_table c
SET FLAG = -1, err_det='Incorrect entry'
WHERE FName is NOT NULL AND
(Status IS NULL OR Status='NEW' OR Status='')
Below just NULL and 'NEW'.
UPDATE test_table c
SET FLAG = -1, err_det='Incorrect entry'
WHERE FName IS NOT NULL AND
(Status IS NULL OR Status='NEW')
