CODEWITHSUNDEEP
postgresqlpostgresql-9.1postgresql-9.2
Ramesh
Ramesh

Reputation: 311

Syntax error - postgresql

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

Upvotes: 1

Views: 107

Answers (3)

shafi7468
shafi7468

Reputation: 433

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)

Upvotes: 0

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 0

user6307701
user6307701

Reputation:

Modify select id, to select table2.id, or select table1.id,

Upvotes: 2

Related Questions