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?

CodePudding user response：

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.

CodePudding user response：

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
...