SQL 在 PostgreSQL 中查看错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/207964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
View error in PostgreSQL
提问by eflles
I have a large query in a PostgreSQL database. The Query is something like this:
我在 PostgreSQL 数据库中有一个大型查询。查询是这样的：
SELECT * FROM table1, table2, ... WHERE table1.id = table2.id...
When I run this query as a sql query, the it returns the wanted row.
当我将此查询作为 sql 查询运行时，它返回所需的行。
But when I tries to use the same query to create a view, it returns an error:
但是当我尝试使用相同的查询来创建视图时，它返回一个错误：
"error: column "id" specified more than once."
“错误：多次指定列“id”。”
(I use pgAdminIII when executing the queries.)
（我在执行查询时使用 pgAdminIII。）
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?
我猜这是因为结果集将有不止一个名为“id”的列。有没有办法解决这个问题，而无需在查询中写入所有列名？
回答by Vinko Vrsalovic
That happens because a view would have two id named columns, one from table1 and one from table2, because of the select *.
发生这种情况是因为视图将有两个 id 命名的列，一个来自 table1，另一个来自 table2，因为 select *。
You need to specify which id you want in the view.
您需要在视图中指定您想要的 id。
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
回答by eflles
If only join columns are duplicated (i.e. have the same names), then you can get away with changing:
如果只有连接列重复（即具有相同的名称），那么您可以避免更改：
select *
from a, b
where a.id = b.id
to:
到：
select *
from a join b using (id)
回答by Ben Wilson
If you got here because you are trying to use a function like to_dateand getting the "defined more than once" error, note that you need to use a column alias for functions, e.g.:
如果您到达这里是因为您尝试使用类似的函数to_date并收到“多次定义”错误，请注意您需要为函数使用列别名，例如：
to_date(o.publication_date, 'DD/MM/YYYY') AS publication_date
回答by Cade Roux
No built-in way in the language to solve it (and frankly, * is a bad practice in general because it can cause latent defects to arise as the table schemas change - you can do table1.*, table2.acolumn, tabl2.bcolumn if you want all of one table and selectively from another), but if PostgreSQL supports INFORMATION_SCHEMA, you can do something like:
语言中没有内置的方法来解决它（坦率地说，* 通常是一种不好的做法，因为它会导致潜在的缺陷随着表模式的变化而出现 - 您可以执行 table1.*、table2.acolumn、tabl2.bcolumn如果您想要所有一个表并有选择地从另一个表中选择），但如果 PostgreSQL 支持 INFORMATION_SCHEMA，您可以执行以下操作：
DECLARE @sql AS varchar
SELECT @sql = COALESCE(@sql + ', ', '')
+ '[' + TABLE_NAME + '].[' + COLUMN_NAME + ']'
+ CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('table1', 'table2')
ORDER BY TABLE_NAME, ORDINAL_POSITION
PRINT @sql
And paste the results in to save a lot of typing. You will need to manually alias the columns which have the same name, of course. You can also code-gen unique names if you like (but I don't):
并将结果粘贴到其中以节省大量输入。当然，您需要手动为具有相同名称的列设置别名。如果您愿意，您还可以编码生成唯一名称（但我不喜欢）：
SELECT @sql = COALESCE(@sql + ', ', '')
+ '[' + TABLE_NAME + '].[' + COLUMN_NAME + '] '
+ 'AS [' + TABLE_NAME + '_' + COLUMN_NAME + ']'
+ CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('table1', 'table2')
ORDER BY TABLE_NAME, ORDINAL_POSITION
