CODEWITHSUNDEEP
sqldatabasepostgresqlselectambiguous
Fofole
Fofole

Reputation: 3548

SQL column reference "id" is ambiguous

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?

Upvotes: 108

Views: 305304

Answers (5)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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.

Upvotes: 0

JScoobyCed
JScoobyCed

Reputation: 10413

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;

Upvotes: 143

Alex Mack
Alex Mack

Reputation: 19

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;

Upvotes: 1

dweeves
dweeves

Reputation: 5605

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

Upvotes: 13

Janaki
Janaki

Reputation: 195

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;

Upvotes: 7

Related Questions