Postgresql column reference "id" is ambiguous

SqlPostgresqlSelect

Sql Problem Overview

I tried the following select:

SELECT (id,name) FROM v_groups vg 
inner join people2v_groups p2vg on vg.id = p2vg.v_group_id
where p2vg.people_id =0;

and I get the following error column reference id is ambiguous.

Thing is if I try the same SELECT but I only ask for name, and not for id also, it works. I'm new to this and maybe I am missing something obvious. Any suggestions?

Thanks.

Sql Solutions

Solution 1 - Sql

You need the table name/alias in the SELECT part (maybe (vg.id, name)) :

SELECT (vg.id, name) FROM v_groups vg 
inner join people2v_groups p2vg on vg.id = p2vg.v_group_id
where p2vg.people_id =0;

Solution 2 - Sql

I suppose your p2vg table has also an id field , in that case , postgres cannot find if the id in the SELECT refers to vg or p2vg.

you should use SELECT(vg.id,vg.name) to remove ambiguity

Solution 3 - Sql

SELECT (vg.id, name) FROM v_groups vg 
INNER JOIN people2v_groups p2vg ON vg.id = p2vg.v_group_id
WHERE p2vg.people_id = 0;

Solution 4 - Sql

SELECT vg.id, 
       vg.name
  FROM v_groups vg INNER JOIN  
       people2v_groups p2vg ON vg.id = p2vg.v_group_id
 WHERE p2vg.people_id = 0;

Solution 5 - Sql

As a additional note: I got this error when I was using a CTE for a join resulting in an ambiguity. I was using the CTE in FROM with an alias and despite prefixing the SELECTed column with the CTE's alias, postgres would still produce this error on the prefixed column call. It was a bit trickier to discover as my query was long.

Hope it helps someone out there.

Categories
Recommended Sql Solutions
Recommended Postgresql Solutions
Recommended Select Solutions

Previous Solution:

milliseconds to time in javascript

JavascriptJquery

Next Solution:

CSS :before and :first-child combined

Css

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionFofoleView Question on Stackoverflow
Solution 1 - SqlJScoobyCedView Answer on Stackoverflow
Solution 2 - SqldweevesView Answer on Stackoverflow
Solution 3 - SqlJanakiView Answer on Stackoverflow
Solution 4 - SqlAlex MackView Answer on Stackoverflow
Solution 5 - SqlRTDView Answer on Stackoverflow