不能简单地使用PostgreSQL表名（“关系不存在”） [英] Cannot simply use PostgreSQL table name ("relation does not exist")

问题描述

我试图运行以下PHP脚本来执行简单的数据库查询：

I'm trying to run the following PHP script to do a simple database query:

$db_host = "localhost";
$db_name = "showfinder";
$username = "user";
$password = "password";
$dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password")
    or die('Could not connect: ' . pg_last_error());

$query = 'SELECT * FROM sf_bands LIMIT 10';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

这会产生以下错误：

查询失败：ERROR：relationsf_bands不存在

Query failed: ERROR: relation "sf_bands" does not exist

在所有示例中，有人得到一个错误，说明关系不存在，这是因为他们在表名称中使用大写字母。我的表名没有大写字母。有没有办法查询我的表而不包括数据库名称，即 showfinder.sf_bands ？

In all the examples I can find where someone gets an error stating the relation does not exist, it's because they use uppercase letters in their table name. My table name does not have uppercase letters. Is there a way to query my table without including the database name, i.e. showfinder.sf_bands?

推荐答案

从我读到的内容，这个错误意味着你没有正确地引用表名。一个常见的原因是，该表是用混合大小写的拼写定义的，并且您试图使用所有小写字母查询它。

From what I've read, this error means that you're not referencing the table name correctly. One common reason is that the table is defined with a mixed-case spelling, and you're trying to query it with all lower-case.

换句话说，以下失败：

CREATE TABLE "SF_Bands" ( ... );

SELECT * FROM sf_bands;  -- ERROR!

使用双引号来分隔标识符，以便可以使用特定的混合大小写拼写，

Use double-quotes to delimit identifiers so you can use the specific mixed-case spelling as the table is defined.

SELECT * FROM "SF_Bands";

---

重新发表评论， search_path，以便当您引用表名称而不限定其模式时，查询将按顺序检查每个模式来匹配该表名称。就像shell中的 PATH 或PHP中的 include_path 等等。您可以检查当前的模式搜索路径： p>

---

Re your comment, you can add a schema to the "search_path" so that when you reference a table name without qualifying its schema, the query will match that table name by checked each schema in order. Just like PATH in the shell or include_path in PHP, etc. You can check your current schema search path:

SHOW search_path
  "$user",public

您可以更改模式搜索路径：

You can change your schema search path:

SET search_path TO showfinder,public;

另请参阅 http://www.postgresql.org/docs/8.3/static/ddl-schemas.html

这篇关于不能简单地使用PostgreSQL表名（“关系不存在”）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！