Postgres column casting...

Question

I have a query

SELECT assetid, type_code, version, name, short_name, status, languages,
charset, force_secure, created, created_userid, updated, updated_userid,
published, published_userid, status_changed, status_changed_userid
FROM sq_ast WHERE assetid = 7

which doesn't work and throws

ERROR: operator does not exist: character varying = integer LINE 4: FROM sq_ast WHERE assetid = 7

I can get it to work by doing

SELECT assetid, type_code, version, name, short_name, status, languages,
charset, force_secure, created, created_userid, updated, updated_userid,
published, published_userid, status_changed, status_changed_userid
FROM sq_ast WHERE assetid = '7'

Please note the quoting of the 7 in the WHERE clause...

I am deploying an huge application and I cannot rewrite the core... similarly I don't want to risk changing the type of the column...

I'm no Postgres expert... please help...

Is there an option for strict casting of columns???

Answer 1

Postgresql has turned more strongly typed in recent versions, and that is a good thing. If assetid is VARCHAR, you can't compare it to an integer (since 8.4, I believe).

In general, is bad design to mix those types: numeric datatypes should be used for real numeric fields, not for strings that just happen to have digits (eg an invoice number). Sometimes, however, the decision is not clear.

Are you potentially interested in doing some arithmetic with your values (sum, substract) ? Then it is an number (INTEGER).

Are (potential) left zeroes to be considered meaninful? In your example, is '07' to be regarded as different from '7'? Then, it is a string (VARCHAR).

In your example, you should check these issues. (is there an assetid that begins with 0? is there some non numeric character? does it seem to be an serial number ?) Depending on this, you might consider to change the field type or (more probable in your scenario) to do a cast , in the prefered direction:

   SELECT... FROM sq_ast WHERE assetid::integer = 7

(if the field in fact is numeric) or elsewhere

   SELECT... FROM sq_ast WHERE assetid = '7'

There's no global setting for reverting the old behaviour, and force an implicit cast for char types, AFAIK.