Thread: Integrity constraint [false] problem

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

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

On 21 Nov 2002, Michael Brewer wrote:

> 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
>
> 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?

Is it possible that one of the referencing tables has the wrong type
for some reason?  The error could be coming from a foreign key check
perhaps.