Can you help me with the below syntax error
Column reference "id" is ambiguous is the error which I am getting.
insert into table1 (id, name, type, version)
select id, '<needs updating>' as name, 'external' as type, -1 as version
from table2
left outer join table1
on table2.id = table1.id
where table1.id is null
group by table2.id, table1.name
order by table2.id, table1.name
As a summary of the answer above and why it is needed (you should accept Anonymous-SOS's answer instead of this one btw since this supplements it).
When PostgreSQL parses the query it notes that id here could refer to table1.id or table2.id and so it throws the error. While it might be possible to note that in this case the two values are necessarily the same, it is probably better to get the error now rather than when you modify the query such that the ambiguity returns.
So you need to specify table1.id or table2.id and qualify the field appropriately.
table1 and table2 both tables having same column name "id". If you run select statement using both tables ,it was confused which table "id" to display.In this case ambiguous error will occur. To overcome this better to use alias name or tablename.columnname (i.e which table column u want that tablename.columnname)
