Integrity constraint [false] problem
От | michaelbrewer@earthlink.net (Michael Brewer) |
---|---|
Тема | Integrity constraint [false] problem |
Дата | |
Msg-id | 77227749.0211210650.71510684@posting.google.com обсуждение исходный текст |
Ответы |
Re: Integrity constraint [false] problem
Re: Integrity constraint [false] problem |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: