Understanding explicit type casts in SQL

Question

SELECT * 
FROM lookup_type 
WHERE type_group = 'enquiry' 
  AND type_sub_group = 'category' 
  AND type_attribute IN (SELECT prod_id 
                         FROM product 
                         WHERE prod_id IN (SELECT option_prod_id 
                                           FROM product_option 
                                           WHERE option_id IN (SELECT link_option_id 
                                                               FROM product_link 
                                                               WHERE link_member_id = '146')));

In the query above I would like to select a record in my lookup table. However I get the following error from my postgreSQL client.

ERROR: operator does not exist: character varying = integer
LINE 1: ...uiry' and type_sub_group='category' and type_attr in(SELECT ...
^

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The type attr is a varchar column and prod_id is a integer column.

What am I doing wrong and how do I fix this? An explanation into how to avoid this in the future would be much appreciated.

Answer 1

PostgreSQL is strongly typed - every value has a defined type, and every function and operator is defined to work with particular types.

There is an operator for comparing two VarChar values, and a different operator for comparing two Integer values. Both happen to be spelled =, but they are separate entries in Postgres's system catalogue. Since there is no operator called = which takes a VarChar on one side and an Integer on the other, this explains the "operator does not exist" message.

In practice, the rule of thumb is that you need to make both sides of an = sign be the same type, so that they can be compared. This can be done using the standard SQL CAST(value AS type) function, which converts a value from one type to another.

In this case, you want the results from your inner SELECT to all be VarChars so that they can be fed into the IN clause, so you can write:

... and type_attribute in (SELECT CAST(prod_id as VarChar) ...

An integer can always be cast to a string, because you can always write out a number using digits. If you know a string will only contain digits, you can also cast the other way, e.g. CAST(type_attribute AS int).

Answer 2

You are checking type_attribute which is a varchar field with an integer i.e prod_id. One thing you can, is to type cast prod_id to varchar in your sub query.

SELECT Cast(prod_id As varchar)