Casting problem - Mailing list pgsql-novice

From John Burski
Subject Casting problem
Date
Msg-id 3BE6E9AC.90602@911ep.com
Whole thread Raw
Responses Re: Casting problem
List pgsql-novice
I'm running PostgreSQL 7.0.3 on a Red Hat 6.2 box and I've encountered a
bit of trouble with casting.

The table I'm dealing with is
         Table "employee_tbl"
   Attribute    |    Type     | Modifier
-----------------+-------------+----------
emp_id          | char(8)     | not null
emp_last_name   | varchar(20) | not null
emp_first_name  | varchar(15) | not null
emp_middle_name | varchar(15) |
emp_gender      | char(1)     | not null
emp_eye_color   | varchar(6)  | not null
emp_hair_color  | varchar(10) | not null
emp_clearance   | char(2)     | not null
emp_job_title   | varchar(20) |
emp_status      | char(1)     | not null
emp_doh         | date        | not null
emp_dob         | date        | not null
emp_id_expires  | date        | not null
Index: employee_tbl_pkey
Constraint: ((emp_gender = 'M'::bpchar) OR (emp_gender = 'F'::bpchar))

The table currently contains only two rows, one each for emp_id 1118 and
1119.  I can execute a "SELECT * FROM employee_tbl WHERE emp_id =
'1119'" and it works flawlessly.  However, when I attempt to execute a
"DELETE FROM employee_tbl WHERE emp_id = '1119'" I get the following
error message:

clocker=# DELETE FROM employee_tbl WHERE emp_id = '1119';
ERROR:  Unable to identify an operator '=' for types 'varchar' and 'bpchar'
   You will have to retype this query using an explicit cast

When I attempt to cast the literal as either a 'varchar' or a 'bpchar'
type I get the same error message.

clocker=# DELETE FROM employee_tbl WHERE emp_id = '1119'::bpchar;
ERROR:  Unable to identify an operator '=' for types 'varchar' and 'bpchar'
   You will have to retype this query using an explicit cast

As a test case, I created a table as follows:

johnb=# create table test2 (
johnb(# mykey char(8) primary key,
johnb(# mydata text
johnb(# );

I inserted data into the table

johnb=# insert into test2 values ('1119', 'Some data' );
INSERT 100432 1
johnb=# insert into test2 values ('1118', 'Some more data' );
INSERT 100433 1

Then I deleted a row

johnb=# delete from test2 where mykey = '1119';
DELETE 1

Which worked flawlessly.  Why does the technique work in one database
table and not the other?  Could my "clocker" database be corrupt?

Thanks in advance for the help.

--
John Burski
I.T. Manager and Systems Administration
911 Emergency Products, Inc.
25 Sixth Avenue North
Saint Cloud, MN  56303
John.Burski@911ep.com

800-863-6911, extension 221
FAX: 800-863-2991
www.911ep.com


pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: problems installing
Next
From: "Josh Berkus"
Date:
Subject: Re: Installing 7.1.3 is missing libssl.0.1