How to SET value to NULL in integer column in case of empty input

Question

When I am trying to use following update query...

UPDATE
            product_table
        SET
            product_name = $1,
            price = $2,
            delivery_interval = NULLIF ($3, '')
        WHERE
            id = $4;

...following appears...

error: column "delivery_interval" is of type integer but expression is of type text

delivery_interval = NULL instead of NULLIF ($3, '') seems to work. Why?

Why is $3 a string instead of an integer? Looks like an application issue, that creates an error in the database. — Frank Heikens
– Frank Heikens, Commented Nov 19, 2021 at 11:47

D.Tabakov · Accepted Answer · 2021-11-19 11:07:03Z

4

As best as I can tell, you need this:

delivery_interval = (NULLIF($3::text, '')::integer)

First you need to cast the argument to text, so NULLIF is comparing like with like, then you cast the whole expression to integer, to match the column type.

answered Nov 19, 2021 at 11:07

D.Tabakov

1964 bronze badges

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

Comments

Saiyam Jain · Accepted Answer · 2023-07-13 06:06:33Z

1

If it is Postgres Then you have to use NULL For integer Datatype

update table_name set status = 'SUCCESS', column2 = NULL where condition <>

answered Jul 13, 2023 at 6:06

Saiyam Jain

1918 bronze badges

Comments

sticky bit · Accepted Answer · 2021-11-19 11:03:33Z

0

From the documentation:

The result has the same type as the first argument — but there is a subtlety. What is actually returned is the first argument of the implied = operator, and in some cases that will have been promoted to match the second argument's type. For example, NULLIF(1, 2.2) yields numeric, because there is no integer = numeric operator, only numeric = numeric.

So either your $3 is text or is implicitly casted to text because of the implicit = nullif() causes. And that makes the result of nullif() a text.

You can try to explicitly cast the result of nullif().

...
nullif($3, '')::integer
...

answered Nov 19, 2021 at 11:03

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to SET value to NULL in integer column in case of empty input

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related