Wrong type in "Update case when then" query

[arzonus]

Hello, guys!

I have trouble with the query.
Table:

CREATE TABLE test (
	id INTEGER NOT NULL,
	order INTEGER
)

Query:

UPDATE test
 SET
 	order = (
 		CASE id
 			WHEN $1 THEN $2
 			WHEN $3 THEN $4
 		END
 		)

If I use psql or IDE and try execute the query, I don't have troubles:

DO $$
DECLARE
   sql text := 'UPDATE test SET order = (CASE id WHEN $1 THEN $2 WHEN $3 THEN $4 END)';
BEGIN
   EXECUTE sql
   USING 1, 1, 2, 2;
END$$;

But when I using pq lib execute the query, I have this error:

"pq: column "order" is of type integer but expression is of type text"

Soo, I don't understand, why I don't have the error in "id" column.

Is it bug in pq or is a normal behavior of PostgreSQL?

I can set type using "CAST", I know..

PS: PostgreSQL 9.6

[johto]

If I use psql or IDE and try execute the query, I don't have troubles:

That's because PL/PgSQL knows that the type of 1 is integer, and tells that to the query processing machinery. If you change the example a bit:

DO $$
DECLARE
   sql text := 'UPDATE test SET order = (CASE id WHEN $1 THEN $2 WHEN $3 THEN $4 END)';
BEGIN
   EXECUTE sql
   USING '1', '1', '2', '2'; -- notice the quotes here
END$$;

you get the same error.

This is not really a problem in the driver. You could try and argue that postgres should be smarter about inferring the types for $2 and $4 in this case, but I would be surprised if you got anywhere with that argument.

I don't see any obvious workarounds, so I'm going to suggest adding the casts.

[arzonus]

@johto Thanks for the answer. But, why does this error only appear after "THEN"?
If I would use bindvars only in conditions, I won't get this error.

UPDATE test
 SET
 	order = (
 		CASE id
 			WHEN $1 THEN 200
 			WHEN $2 THEN 300
 		END
 		)

[johto]

The expressions 200 and 300 are of type integer. If use use an unknown type literal (e.g. '200') you should see the same problem again.

[kostya-sh]

Probably the driver could help the database to infer the types here? If I understand correctly lib/pq doesn't send argument types along with their values and leaves the database to infer them. Unfortunately database/sql doesn't allow specifying argument types explicitly either. See golang/go#12383 and referenced issue.

…

On Mar 21, 2017 3:43 PM, "Marko Tiikkaja" ***@***.***> wrote: The expressions 200 and 300 are of type integer. If use use an unknown type literal (e.g. '200') you should see the same problem again. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#586 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AGy9A0NyX0L5Xb3kTqwZdWcyGpbpAImeks5rn_AOgaJpZM4Mirrc> .

[johto]

Probably the driver could help the database to infer the types here?

I can't get excited about that idea. That would mean that queries done through Prepare() would act differently (because we can't know the parameter types at that point) from plain Query() calls. It could also break applications that previously relied on type inference in some cases; function calls are particularly vulnerable here.

Besides, if the application already knows it's an integer, it shouldn't be that difficult to tell that to the database in the query.

[johto]

It could also break applications that previously relied on type inference in some cases; function calls are particularly vulnerable here.

Just so people understand how vulnerable, exactly:

=# create function foof(a text, b int) returns int as $$ select 1 $$ language sql;
CREATE FUNCTION
=# prepare qwr as select foof($1, $2); -- both types unknown; current behavior
PREPARE
=# prepare qwr2(unknown, bigint) as select foof($1, $2); -- proposed new behavior
ERROR:  function foof(unknown, bigint) does not exist
LINE 1: prepare qwr2(unknown, bigint) as select foof($1, $2);

This is because unknown types are not resolved in function calls unless the known types match exactly. And we have to assume the type is "bigint", because database/sql doesn't expose more information than that. This would break WAY too many apps.