bad error message

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