不能简单地使用 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());

这会产生以下错误:

查询失败:错误:关系sf_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 等，您可以检查您当前的架构搜索路径:

---

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屋！