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
