[SOLVED] `column "..." specified more than once` error of view in PostgreSQL

`column "..." specified more than once` error of view in PostgreSQL

I have a large query in a PostgreSQL database. The Query is something like this:

SELECT * FROM table1, table2, ... WHERE table1.id = table2.id...

When I run this query as a sql query, the it returns the wanted row.

But when I tries to use the same query to create a view, it returns an error:

error: column "id" specified more than once.

(I use pgAdminIII when executing the queries.)

I'll guess this happens because the resultset will have more than one column named "id". Is there someway to solve this, without writing all the column names in the query?

Solution

That happens because a view would have two id named columns, one from table1 and one from table2, because of the select *.

You need to specify which id you want in the view.

SELECT table1.id, column2, column3, ... FROM table1, table2 
WHERE table1.id = table2.id

The query works because it can have equally named columns...

postgres=# select 1 as a, 2 as a;
 a | a
---+---
 1 | 2
(1 row)

postgres=# create view foobar as select 1 as a, 2 as a;
ERROR:  column "a" duplicated
postgres=# create view foobar as select 1 as a, 2 as b;
CREATE VIEW