Re: Integrity constraint [false] problem

michaelbrewer(at)earthlink(dot)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