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

Which PostgreSQL version? a quick test suggests that 8.4 allows this. — araqnid
– araqnid, Commented May 18, 2010 at 19:17

leonbloy · Accepted Answer · 2013-07-31 21:32:01Z

8

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

In general (not only in Postgresql, not only in database design) it's bad design to mix those types: numeric datatypes should be used for real numeric fields, and not for strings that just happen to consist of digits.

For example, an invoice "number", or a credit card "number", should not normally be represented as "number", but as a string.

Sometimes, however, the decision is not clear (eg: document numbers).

Some criteria that can help:

Are you potentially interested in doing arithmetic with your values (sum, substract)? Would that at least make sense? Then, it's an number.
Should zeroes on the left be preserved, or considered relevant? (is '07' to be regarded as different from '7'?) Then, it is a string.

Depending on how you anwser these questions in your scenario (Is there an assetid that begins with 0? Could there be some non numeric character? Does it seem to be an serial number ?), 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 you decide that the field is numeric) or elsewhere

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

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

edited Jul 31, 2013 at 21:32

answered May 18, 2010 at 20:04

leonbloy

76.4k22 gold badges149 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rup Over a year ago

"There's no global setting for reverting to the old behaviour" - found on another answer here, you can create implicit cast functions to mimic the old behaviour e.g. using the script on the PostgreSQL wiki.

Collectives™ on Stack Overflow

Postgres column casting

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related