PostgreSQL in list, mixing string and int-CodePudding

We are using PostgreSQL 11 and have a query from Redmine database. It is a query that works fine in MySQL 8 but on PostgreSQL we get an error.

SELECT fixed_version_id 
FROM issues WHERE 
((issues.fixed_version_id IN ('current_version','2')));

ERROR: invalid input syntax for integer: "current_version" LINE 1: ...d FROM issues WHERE ((issues.fixed_version_id IN ('current_v...

I understand that fixed_version_id is an int and that I quering strings. However, is other SQL like MySQL 8 you can do this and it actually returns values. But in PostgreSQL we get an error. Not sure if we have it setup wrong or if this is just the way PostgreSQL works?

Any help would be most appreciated thank you.

We ran this query

SELECT fixed_version_id 
FROM issues 
WHERE ((issues.fixed_version_id IN ('current_version','2')));

We were expecting Not to get an error.

CodePudding user response：

SQL is a tightly typed language (seems MySql does not adhere to the standard). The only correction is using the correct type - in this case integer. But you can `CAST' an integer to text and compare.

SQL Standard:

WHERE ((cast (issues.fixed_version_id as text) IN ('current_version','2')));

Postgresql extension:

WHERE ((issues.fixed_version_id::text IN ('current_version','2')));

CodePudding user response：

In your script we see that you need to put values of varchar types in WHERE clause - ('1', '2', ...) and not (1, 2, ...)

If it is so than @Belayer 's answer will help you.