SQL column reference "id" is ambiguous

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

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; 

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; 

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.