Postgres Casting
I have a request
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'
Notice the quoting 7 in the WHERE ...
I am deploying a huge application and I cannot rewrite the core ... Likewise, I do not want to risk changing the column type ...
I'm not a Postgres expert ... please help ...
Is there a possibility of strict casting of columns ???
a source to share
Postgresql is more strongly typed in recent versions, which is a good thing. If assetid
is VARCHAR, you cannot compare it to an integer (from 8.4, I suppose).
In general (not only in Postgresql, not only in database design) it is bad design for mixing these types: numeric data types should be used for real numeric fields, not for strings that are just numbers.
For example, an invoice number or credit card number should not normally be presented as a number but as a string.
Sometimes, however, the decision is not clear (eg document numbers).
Some criteria that might help:
-
Are you potentially interested in doing arithmetic on your values (sum, subtraction)? Would that at least make sense? Then, this is the number .
-
Should the zeros on the left hold or are considered relevant? (Is "07" considered other than "7"?) Then it's a string .
Depending on how you ask these questions in your script (is there an activat that starts with 0? Could there be some non-digit character? It seems to be a serial number?), You might consider changing the field type, or (more likely , in your script) to cast in your preferred direction:
SELECT... FROM sq_ast WHERE assetid::integer = 7
(if you decide the field is numeric) or elsewhere
SELECT... FROM sq_ast WHERE assetid = '7'
No global settings to revert to old behavior and enforce inactive letters for character types, AFAIK.
a source to share