Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

View error 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?

like image 299
eflles Avatar asked Oct 16 '08 09:10

eflles


People also ask

How do I view PostgreSQL views?

The PostgreSQL views are created using the CREATE VIEW statement. The PostgreSQL views can be created from a single table, multiple tables, or another view. CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

How do I see query history in PostgreSQL?

in your command line, try \s . This will list the history of queries, you have executed in the current session.


1 Answers

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
like image 52
Vinko Vrsalovic Avatar answered Sep 22 '22 08:09

Vinko Vrsalovic