查看PostgreSQL中的错误 [英] View error in PostgreSQL

问题描述

我在PostgreSQL数据库中有一个大型查询. 查询是这样的:

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

当我将此查询作为sql查询运行时，它将返回所需的行.

但是当我尝试使用相同的查询创建视图时，它返回错误:

错误:列"id"指定了多次."

(我在执行查询时使用pgAdminIII.)

我猜会发生这种情况，因为结果集将包含多个名为"id"的列.是否有某种方法可以解决此问题，而无需在查询中编写所有列名?

解决方案

之所以会发生这种情况，是因为视图会由于选择*而具有两个ID命名列，一个来自table1，另一个来自table2.

您需要指定要在视图中使用的ID.

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

查询之所以有效，是因为它可以具有同名的列...

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

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?

解决方案

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

这篇关于查看PostgreSQL中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！