SQL column reference "id" is ambiguous

Question

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.

If I try the same SELECT, but I only ask for name and not for id also, it works.

Any suggestions?

Demonstrate

Once you have more than one table in your statement you should alway prefix your column names with the corresponding table alias. That way you'll never get this error. – Heteropterous

This issue actually happens when there is same column name in both tables. where <tableName.columnName> = <value> can solve this issue. – Dandridge

Answer 1

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;

Trickster

Answer 2

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

Nombles

Yeah as the matter of fact the id field is duplicate. – Demonstrate

Answer 3

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;

Affirmation

Answer 4

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;

Subordinate

Answer 5

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.

Kowtow

what is the solution? I have the same problem – Sippet

Answer 6

I created person table, then inserted 2 rows into it as shown below:

CREATE TABLE person (
  id INTEGER,
  name VARCHAR(20)
);

INSERT INTO person (id, name) 
VALUES (1, 'John'), (2, 'David');

Then, I created my_func() which returns person table as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(id INTEGER, name VARCHAR(20))
AS $$
BEGIN
  RETURN QUERY SELECT id, name FROM person;
END;        -- ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑
$$ LANGUAGE plpgsql;

Finally, calling my_func() got the same error as shown below:

postgres=# SELECT my_func();
ERROR:  column reference "id" is ambiguous
LINE 1: SELECT id, name FROM person
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT id, name FROM person
CONTEXT:  PL/pgSQL function my_func() line 3 at RETURN QUERY

So, I set the table name person with . just before id and name as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(id INTEGER, name VARCHAR(20))
AS $$
BEGIN
  RETURN QUERY SELECT person.id, person.name FROM person;
END;               -- ↑ ↑ ↑ ↑    ↑ ↑ ↑ ↑
$$ LANGUAGE plpgsql;

Finally, I could call my_func() without error as shown below:

postgres=# SELECT my_func();
  my_func
-----------
 (1,John)
 (2,David)
(2 rows)

*Memos:

• Omitting FROM clause from SELECT statement gets the error

• If the number of the columns in RETURNS TABLE(...) doesn't match the number of the returned columns, there is the error.

Axenic