CODEWITHSUNDEEP
postgresql
Stefan Walker
Stefan Walker

Reputation: 13

PostgreSQL - Error column reference "id" is ambiguous

I've aliased everything, but can't seem to get this query working without the

error column reference \"id\" is ambiguous"

It seems to work if i remove one of the joins, but i'm just confused as to why it won't work with two?

create function influence.person_info(user_id integer)
  returns setof influence.person_object as
$$
    declare
    obj influence.person_object;
    begin
        select t1.email as a_email, t2.organisation_url as a_org, t3.first_name as a_first, t3.last_name as a_last into obj
        from influence_private.person_account as t1
        inner join influence_private.organisation_account as t2 on (t1.organisation_id = t2.id)
        inner join influence.person as t3 on (t1.person_id = t3.id)
        where id = $1;
        return next obj;
    end;
$$ LANGUAGE plpgsql stable;

Any pointers?

Upvotes: 1

Views: 10767

Answers (3)

Ragmar
Ragmar

Reputation: 350

The problem of your query is t2 and t3 both tables have a column called id. In the Where clause id is ambigous because it doesn't know what id you are refering (t2 or t3) specify it and it will work properly.

Example fix:

create function influence.person_info(user_id integer)
  returns setof influence.person_object as
$$
    declare
    obj influence.person_object;
    begin
        select t1.email as a_email, t2.organisation_url as a_org, t3.first_name as a_first, t3.last_name as a_last into obj
        from influence_private.person_account as t1
        inner join influence_private.organisation_account as t2 on (t1.organisation_id = t2.id)
        inner join influence.person as t3 on (t1.person_id = t3.id)
        where t1.id = $1;
        return next obj;
    end;
$$ LANGUAGE plpgsql stable;

Upvotes: 0

Paul Coleman
Paul Coleman

Reputation: 475

In your where clause you will need to explicitly state which id you are referring to.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

If you check your query:

select t1.email as a_email, ...
from influence_private.person_account as t1
join influence_private.organisation_account as t2 
  on (t1.organisation_id = t2.id)   -- here
join influence.person as t3 
  on (t1.person_id = t3.id)         -- here

Both your tables (influence_private and influence) have column named id. Postgresql doesn't know which one you want to use so you have to use full name like t2.id.

Upvotes: 0

Related Questions