michaelbrewer@earthlink.net (Michael Brewer) writes:
> 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
This is not an integrity constraint message...
I suspect that the error is not coming from analysis of your UPDATE,
but from some other operation triggered by the update. Have you got any
rules or triggers on student_information?
> 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.
That is bizarre.
I would be inclined to try to gather more information by attaching to
the backend with a debugger, setting a breakpoint at elog(), and then
obtaining a stack backtrace from the point at which these errors are
reported. Can you provide that info?
regards, tom lane