Thread: 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
anexplicit 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
anexplicit cast
 

what could be the problem here?

Thankx in advance

With Regards,
Bhuvaneswar.

Bhuvan A <bhuvansql@yahoo.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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco