指定模式名称后,为什么PostgreSQL SELECT查询返回不同的结果? [英] Why does a PostgreSQL SELECT query return different results when a schema name is specified?
问题描述
我有一个包含4列的PostgreSQL数据库表-标记为column_a,column_b等。我想使用一个简单的选择查询查询该表:
I have a PostgreSQL database table with 4 columns - labeled column_a, column_b, etc. I want to query this table with a simple select query:
select * from table_name;
我得到一些结果如下:
column_a | column_b
---------+---------
'a value'|'b_value'
但是当我使用此查询时:
But when I use this query:
select * from schema_name.table_name;
我得到了完整的结果:
column_a | column_b | column_c | column_d
---------+----------+----------+---------
'a value'|'b value' |'c value' |'d_value'
列<$创建初始表后,在以后的日期中添加了c $ c> c 和 d
。我的问题是:在选择查询中遗漏了架构名称时,为什么数据库会忽略后面的列?
Columns c
and d
were added at a later date, after initial table creation. My question is: Why would the database ignore the later columns when the schema name is left out of the select query?
推荐答案
表名称在Postgres中的数据库中不是唯一的。在不同的模式中,可以有任意数量的名为 table_name的表-包括临时模式,除非您在 search_path
中的其他模式之后明确列出,否则临时模式始终排在第一位。显然,有个名为 table_name
的表。必须正确理解 search_path
的作用:
Table names are not unique within a database in Postgres. There can be any number of tables named 'table_name' in different schemas - including the temporary schema, which always comes first unless you explicitly list it after other schemas in the search_path
. Obviously, there are multiple tables named table_name
. You must understand the role of the search_path
to interpret this correctly:
- How does the search_path influence identifier resolution and the "current schema"
第一个表位于以下模式中:在 search_path
中的 schema_name
之前(或 schema_name
不是在那里列出)。因此,不合格的表名将解析为该表(或视图)。检查当前角色在数据库中有权访问的名为 table_name的表的列表:
The first table lives in a schema that comes before schema_name
in your search_path
(or schema_name
is not listed there at all). So the unqualified table name is resolved to this table (or view). Check the list of tables named 'table_name' that your current role has access to in your database:
SELECT *
FROM information_schema.tables
WHERE table_name = 'table_name';
视图只是带有附加<< c $ c> RULE 内部。它们可以起到与常规表相同的作用,并包含在上面的查询中。
详细信息:
Views are just special tables with an attached RULE
internally. They could play the same role as a regular table and are included in the above query.
Details:
- How to check if a table exists in a given schema
这篇关于指定模式名称后,为什么PostgreSQL SELECT查询返回不同的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!