Thread: bad error message
I got a really bad error message in postgres on a CREATE TABLE in 8.1.0: ERROR: column "id" referenced in foreign key constraint does not exist That seems odd-- I mean, I know I obviously made an error. I'm just used to more detailed errors. I didn't see anything in the changelogs since 8.1.0 saying that it was addressed-- I could have missed that-- but it also might not have been. So... where can i complain that the error message should include the field name I tried to toss the references constraint in ? // Jonathan Vanasco
am Thu, dem 12.10.2006, um 15:27:08 -0400 mailte Jonathan Vanasco folgendes: > > I got a really bad error message in postgres on a CREATE TABLE in 8.1.0: > > ERROR: column "id" referenced in foreign key constraint does not > exist > > That seems odd-- I mean, I know I obviously made an error. I'm just > used to more detailed errors. Can you show us your SQL? The message is clear: you create a new table with a foreign key to an other table that doesn't exist. An example: -- first, i create a table with a primary key test=# create table t1 (id int primary key, foo text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE -- now i create a new table with a foreign key constraint to the first -- table test=# create table t2 (id int references t1(id)); CREATE TABLE -- -- and now i make a mistake -- test=# create table t3 (id int references t1(id_)); ERROR: column "id_" referenced in foreign key constraint does not exist HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Oct 12, 2006, at 3:44 PM, A. Kretschmer wrote: > Can you show us your SQL? The message is clear: you create a new table > with a foreign key to an other table that doesn't exist. An example: Yes, I know that part. The error message is bad though, because it doesn't tell me exactly where the error is. I got as an error ERROR: column "id" referenced in foreign key constraint does not exist I should have gotten something like ERROR: column "id" referenced in foreign key constraint on column "xyz" table "abc" does not exist ( the table "abc" is not necessary, i just wanted to be explicit about the message ) In that create table statement, i had 10 columns each referencing an 'id' in another column. I like very normalized DBs. I had to go through each column individually to see where my error was. Postgres should have immediately told me which of the source table columns that constraint failed on-- not just about the target column name.
Jonathan Vanasco <postgres@2xlp.com> writes: > Yes, I know that part. The error message is bad though, because it > doesn't tell me exactly where the error is. > I got as an error > ERROR: column "id" referenced in foreign key constraint does not exist > I should have gotten something like > ERROR: column "id" referenced in foreign key constraint on column > "xyz" table "abc" does not exist That's not necessarily all that much help, if you've got so many FK constraints in your command that you don't know exactly where to look. I think what you're really wishing for is an error cursor position. 8.2 has the infrastructure for this, eg regression=# create table foo (a int, b int, c int); CREATE TABLE regression=# select a, b, q from foo; ERROR: column "q" does not exist LINE 1: select a, b, q from foo; ^ regression=# but unfortunately the facility hasn't been extended to foreign key constraint clauses in particular :-(. Maybe next time. regards, tom lane
On Oct 12, 2006, at 7:39 PM, Tom Lane wrote: > That's not necessarily all that much help, if you've got so many FK > constraints in your command that you don't know exactly where to look. > I think what you're really wishing for is an error cursor position. > 8.2 has the infrastructure for this, eg > > regression=# create table foo (a int, b int, c int); > CREATE TABLE > regression=# select a, b, q from foo; > ERROR: column "q" does not exist > LINE 1: select a, b, q from foo; > ^ > regression=# > > but unfortunately the facility hasn't been extended to foreign key > constraint clauses in particular :-(. Maybe next time I'll be glad if it makes it into a future release. It would be a GREAT feature. Thats for the info on the cursor position. thats a nice update in the .2 branch.
am Thu, dem 12.10.2006, um 19:39:37 -0400 mailte Tom Lane folgendes: > I think what you're really wishing for is an error cursor position. > 8.2 has the infrastructure for this, eg > > regression=# create table foo (a int, b int, c int); > CREATE TABLE > regression=# select a, b, q from foo; > ERROR: column "q" does not exist > LINE 1: select a, b, q from foo; > ^ > regression=# Very cool feature ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net