在 postgresql 中，“数据库”和“关系”有什么区别？('错误关系 x 不存在', '错误数据库 x 已经存在')
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12232640/
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
In postgresql, what's the difference a "database" and a "relation"? ('error relation x does not exist', 'error database x already exists')
提问by boulder_ruby
I see the juxtaposition of these two errors and, given the dearth of Google search results, had to ask. What is the difference and what do I need to be doing here?
我看到这两个错误并存，鉴于谷歌搜索结果的缺乏，不得不问。有什么区别，我需要在这里做什么？
deploy=# GRANT SELECT ON angel_research_production TO angel_research;
ERROR: relation "angel_research_production" does not exist
deploy=# create database angel_research_production;
ERROR: database "angel_research_production" already exists
My guess is that I need to be doing this grant select business from some other user...
我的猜测是我需要从其他一些用户那里做这个授予选择业务......
So I run this on postgres (dbroot) and get this:
所以我在 postgres (dbroot) 上运行它并得到这个：
postgres=# GRANT SELECT ON angel_research_production TO angel_research;
ERROR: relation "angel_research_production" does not exist
So it does exist as a database, but not as a relation. How might I rectify this and what are the underlying issues here? I'm a little overwhelmed. Thanks
所以它确实作为数据库存在，但不是作为关系存在。我该如何解决这个问题，这里的潜在问题是什么？我有点不知所措。谢谢
回答by Craig Ringer
My guess is that you really want to recursively GRANTthe SELECTright to every relation (table and view) within the database angel_research_production. Correct?
我的猜测是你真的想递归地获得数据库中每个关系（表和视图）GRANT的SELECT权利angel_research_production。正确的？
How to grant on all tables in a database
如何授予数据库中的所有表
If so, in PostgreSQL 9.0 and above you have:
如果是这样，在 PostgreSQL 9.0 及更高版本中，您有：
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
from the manual for GRANT. Note the ALL TABLES IN SCHEMAclause. Usage:
来自GRANT的手册。注意ALL TABLES IN SCHEMA条款。用法：
GRANT SELECT ON ALL TABLES IN SCHEMA public TO angel_research;
If all your user-defined objects are in the publicschema(see below) that'll do the trick.
如果您所有的用户定义对象都在public架构中（见下文），那就可以了。
In prior versions there is no such feature, but user defined functions exist as workarounds.
在以前的版本中没有这样的功能，但用户定义的功能作为解决方法存在。
Pg 9.0 also has ALTER DEFAULT PRIVILEGES, which changes the defaultprivileges assigned to newly createdobjects. It does not affect existing objects.
Pg 9.0 也有ALTER DEFAULT PRIVILEGES，它改变了分配给新创建对象的默认权限。它不会影响现有对象。
What does the error message mean?
错误信息是什么意思？
As noted by TokenMacGuy, a relation is a table or view, not a database.
正如 TokenMacGuy 所指出的，关系是一个表或视图，而不是一个数据库。
GRANT SELECT ON angel_research_production TO angel_research;
can be thought of as shorthand for:
可以被认为是以下的简写：
GRANT SELECT ON TABLE angel_research_production TO angel_research
^^^^^
and that table(relation) doesn't exist, so you're getting the error reported above.
并且该表（关系）不存在，因此您会收到上面报告的错误。
In the manual for GRANTor the psql\h GRANToutput you'll see:
在GRANT或psql\h GRANT输出的手册中，您将看到：
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
This shows that the privileges you can GRANTto a database are CREATE, CONNECTand TEMPORARY. There is no SELECTright on a database.
这表明您可以GRANT对数据库拥有的权限是CREATE、CONNECT和TEMPORARY。SELECT数据库没有权限。
Relations? Schema? Huh?
关系？架构？嗯？
There are four levels of organisation in Pg:
Pg中有四个层次的组织：
Cluster- controlled by the postmaster, accepts connections on a given IP/port combo, contains one or more databasesincluding the built-in
template0,template1andpostgresdatabases. Controlled bypostgresql.confandpg_hba.conf. Your DB cluster is often created for you by an installer or package. Not to be confused with the normal meaning of cluster as a compute clusteror the general english language meaning.Database- contains one or more schemataor schemas. You connect to a specific database when connecting to Pg.
Schema- contains objectsincluding relations. If you don't specify otherwise, anything user-created goes into the
publicschema. Queries can reference objects in multiple schema explicitly or, via search_path, implicitly.Objects- Somewhat PostgreSQL specific, anything (including a relation) that exists in a schema.
集群- 由 postmaster 控制，接受给定 IP/端口组合上的连接，包含一个或多个数据库，包括内置的
template0,template1和postgres数据库。由postgresql.conf和控制pg_hba.conf。您的数据库集群通常由安装程序或软件包为您创建。不要与作为计算集群的集群的正常含义或一般的英语语言含义混淆。数据库-包含一个或多个图式或模式。连接到 Pg 时，您连接到特定的数据库。
架构- 包含对象，包括关系。如果您没有另外指定，则用户创建的任何内容都会进入
public架构。查询可以显式地或通过search_path隐式地引用多个模式中的对象。对象- 某种特定于 PostgreSQL 的东西，存在于模式中的任何东西（包括关系）。
回答by SingleNegationElimination
a relation is a table (or something that looks like one, eg a view), that is, it is a collection of rows, all with the same fields, and given some name to reference them by.
关系是一个表（或看起来像一个表的东西，例如视图），也就是说，它是行的集合，所有行都具有相同的字段，并给出了一些名称来引用它们。
A database is a collection of relations and other entities (like triggers, functions and rules) that are kept together in some logical grouping.
数据库是在某种逻辑分组中保存在一起的关系和其他实体（如触发器、函数和规则）的集合。
