Re: Strange Problem As Type Casting

hi all,

i am using postgresql 7.1.
here i am facing some strange problem for last 2 days.

i have a table 'users' as below

test=> \d users
Table "users"
Attribute | Type | Modifier
-------------------+--------------------------+-----------------------------------------------
user_id | integer | not null default nextval('user_id_seq'::text)
login_id | text |
user_code | text |
comp_code | text |
user_name | text |
password | text |
last_login | timestamp with time zone |
noof_logins | integer |
dob | date |
eff_date | date |
is_active | boolean | default 'f'
email_id | text |
creation_time | timestamp with time zone |
modification_time | timestamp with time zone |
Indices: users_login_id_key,
users_pkey,
users_user_code_key
Constraint: ((email_id ~ '@'::text) AND (email_id ~ '.'::text))

----

here user_id is of type integer and not serial. ie.. i explicitly created
the sequence and had it as default.
while i update user_id field

test=> UPDATE users set user_id=1 ,login_id='admin' where user_id='1346';
ERROR: Unable to identify an operator '=' for types 'text' and 'int4'
You will have to retype this query using an explicit cast

again i tried it as below
test=> UPDATE users set login_id='admin' where user_id='1346';
UPDATE 1
test=> UPDATE users set user_id='1' where login_id='admin';
ERROR: Unable to identify an operator '=' for types 'text' and 'int4'
You will have to retype this query using an explicit cast

what could be the problem here?

Thankx in advance

With Regards,
Bhuvaneswar.

Bhuvan A <bhuvansql(at)yahoo(dot)com> writes:
> test=> UPDATE users set user_id=1 ,login_id='admin' where user_id='1346';
> ERROR: Unable to identify an operator '=' for types 'text' and 'int4'
> You will have to retype this query using an explicit cast

Got any triggers, rules, or foreign keys for this table? The error is
not necessarily in the command you typed, it could be in subsidiary
processing.

regards, tom lane

Bhuvanm

> test=> UPDATE users set user_id=1 ,login_id='admin' where
> user_id='1346';
> ERROR: Unable to identify an operator '=' for types 'text' and
> 'int4'
> You will have to retype this query using an explicit cast

Stop quoting your integers. It should be
WHERE user_id = 1346

No quotes for numbers.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco