CODEWITHSUNDEEP
sqlpostgresql
Jeb50
Jeb50

Reputation: 7077

Column reference is NOT ambiguous

There are many threads on

column reference "something" is ambiguous

This is typical in most RDBMS when more than one tables involved have the same column name. However, all my tables have an unique prefix for their own columns therefore no any columns in the schema would share the same name. Reason being is in a big query, it's easy to know where a column is from! For example productid in order table is odrproductid, and in product table is pdtproductid, so this query ran perfectly in pgAdmin query tool:

select pdtproductid
from orders
join product on odrproductid = pdtproductid and ...

Paste into a function and call this function.

ERROR:  column reference "odrproductid" is ambiguous
LINE 3:  join product on odrproductid = ...
                         ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table
column.

Obviously it can be resolved by adding alias and prefix columns in JOIN. But there is no variables defined in the function, and if it ran perfect interactively why not in the function? And added an alias for orders, it will pick pdtproductid and so on. If it's really an ambiguous column, logically it should have picked up from the up-front SELECT list.

Solution, paste the exact good sql into a variable and run it as dynamic SQL, it goes well. For example

AS $BODY$
declare pgsql text ;
begin 
  pgsql := 'select ... from orders join product on odrproductid = pdtproductid and ...';
  return query execute pgsql;
end;

Upvotes: 0

Views: 1040

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248295

There is a PL/pgSQL variable, a function parameter or a return parameter with the same name. Avoid ambiguity by qualifying column references with the table name:

select pdtproductid
from orders
join product on orders.odrproductid = product.pdtproductid and ...

Upvotes: 2

Related Questions