Hello, all; we've suddenly started seeing some really odd behavior in
one of our PostgreSQL 7.2.3 [Solaris] databases. For some reason,
even though the primary key to our student_information table is
CHAR(9), any attempt to update any primary key fails:
UPDATE student_information
SET student_id='123456789'
WHERE student_id='123456798'
triggers an integrity constraint:
ERROR: column "student_id" is of type 'integer' but expression is of
type 'character'
You will need to rewrite or cast the expression
However, the table was defined as CHAR(9); doing a \d shows the type
as being character(9), and the output of:
select * from pg_attribute
where attname='student_id'
and attrelid = (SELECT oid FROM pg_class
WHERE relname = 'student_information');
shows an atttypid of 1042 and an atttypmod of 13 (so it's the correct
type [bpchar] and of correct length [9]). As recently as last week,
we were able to update student_ids with no problems; we haven't
changed or updated PostgreSQL over that period of time, either.
Even more strangely, we've discovered one table that doesn't allow
inserts if the student_id foreign key starts with 0:
INSERT INTO this_other_table(student_id, another_foreign_key)
VALUES('000000100', 1);
gives us:
ERROR: <unnamed> referential integrity violation - key referenced
from this_other_table not found in student_information
even though 000000100 *is* in student_information (and it works for
all student_ids that don't start with 0). Other tables allow the same
insert (of 000000100) without firing off referential integrity
violations.
How can we fix these problems? Why would PostgreSQL think that
student_id is of type integer when it's character(9)? Is there
anything that might have caused this to start occuring that we can
avoid?
Thanks for your help---
---Michael Brewer
michaelbrewer@earthlink.net