0

How is it possible to get this error? Situation is this : Table a has columns a,b,c,e,... and pkey is a,b,c. Table b has columns a,b,c, and d and pkey is a,b,c,d

ALTER TABLE b ADD CONSTRAINT a_b_fkey FOREIGN KEY (a, b, c) REFERENCES a (a,b,c)

ERROR: column reference "b" is ambiguous.

Am I missing something? This doesn't seem to make any sense.

Jocken
  • 160
  • 9
  • Please add the table definitions for tables a and b to your question (the output of `\d a` and `\d b` in psql) – joop Dec 12 '14 at 17:22
  • Sorry, don't know what "\d" means. Create table a (a integer, b integer, c integer, e text, f text); Alter table a Add constraint a_pkey Primary key (a,b,c); Create table b (a integer, b integer, c integer, d text); Alter table b Add constraint b_pkey Primary key (a,b,c,d); – Jocken Dec 12 '14 at 19:33
  • Cannot reproduce in PostgreSQL 9.3. – Mike Sherrill 'Cat Recall' Dec 12 '14 at 19:56
  • I am also using 9.3. try this: Create table a (a integer, rowid integer, c integer, e text, f text); Alter table a Add constraint a_pkey Primary key (a,rowid,c); Create table b (a integer, rowid integer, c integer, d text); Alter table b Add constraint b_pkey Primary key (a,rowid,c,d); Then ALTER TABLE b ADD CONSTRAINT b_a_fkey FOREIGN KEY (a, rowid, c) REFERENCES a (a, rowid, c); – Jocken Dec 12 '14 at 20:49
  • Works fine here. No errors. – Mike Sherrill 'Cat Recall' Dec 12 '14 at 22:01

1 Answers1

0

The problematic underlying query that postgres was running as part of the alter statement was this:

SELECT 
    fk."a",
    fk."b",
    fk."c"
FROM ONLY "public"."b" fk 
JOIN ONLY "public"."a" pk 
    ON ( pk."a" OPERATOR(pg_catalog.=) fk."a" 
        AND pk."b" OPERATOR(pg_catalog.=) fk."b" 
        AND pk."c" OPERATOR(pg_catalog.=) fk."c")
WHERE pk."a" IS NULL
    AND (fk."a" IS NOT NULL 
        AND fk."b" IS NOT NULL
        AND fk."b" IS NOT NULL);

So I changed the names, but it apparently was important that the name of column "b" in both tables was actually "rowid". After speaking with my DBA for a while, we just guessed that maybe rowid was a reserved word in postgres or at least the bug was related to that. Turns out it was; I renamed the column in both tables to "row_id" and the problem was resolved. Doesn't work:

SELECT 
    fk."templateid",
    fk."rowid",
    fk."colid"
FROM ONLY "public"."b" fk 
JOIN ONLY "public"."a" pk 
    ON ( pk."templateid" OPERATOR(pg_catalog.=) fk."templateid" 
        AND pk."rowid" OPERATOR(pg_catalog.=) fk."rowid" 
        AND pk."colid" OPERATOR(pg_catalog.=) fk."colid")
WHERE pk."templateid" IS NULL
    AND (fk."templateid" IS NOT NULL 
        AND fk."rowid" IS NOT NULL
        AND fk."colid" IS NOT NULL);

Works :

SELECT 
    fk."templateid",
    fk."row_id",
    fk."col_id"
FROM ONLY "public"."b" fk 
JOIN ONLY "public"."a" pk 
    ON ( pk."templateid" OPERATOR(pg_catalog.=) fk."templateid" 
        AND pk."row_id" OPERATOR(pg_catalog.=) fk."row_id" 
        AND pk."col_id" OPERATOR(pg_catalog.=) fk."col_id")
WHERE pk."templateid" IS NULL
    AND (fk."templateid" IS NOT NULL 
        AND fk."row_id" IS NOT NULL
        AND fk."col_id" IS NOT NULL);

(I just changed colid for naming consistency, it was not contributing to the problem)

Jocken
  • 160
  • 9
  • ["rowid" is not a reserved word in PostgreSQL](http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html). How did you determine that PostgreSQL executed this code to create a foreign key constraint? – Mike Sherrill 'Cat Recall' Dec 12 '14 at 19:56
  • I ran the create constraint command in pgadmin and that query was printed out in the error box. I then ran the query independently and was able to replicate the ambiguity error. I don't mean it's an official reserved word, I meant more that the name is being treated specially in postgres' code – Jocken Dec 12 '14 at 20:46