php 不能简单地使用 PostgreSQL 表名（“关系不存在”）

Cannot simply use PostgreSQL table name ("relation does not exist")

提问by Keyslinger

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

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

$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());

This produces the following error:

这会产生以下错误：

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

查询失败：错误：关系“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?

在所有示例中，我都可以找到有人收到错误说明关系不存在的地方，这是因为他们在表名中使用了大写字母。我的表名没有大写字母。有没有办法在不包含数据库名称的情况下查询我的表，即showfinder.sf_bands？

回答by Bill Karwin

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.

根据我的阅读，此错误意味着您没有正确引用表名。一个常见的原因是该表是用大小写混合的拼写定义的，而您试图用所有小写字母来查询它。

In other words, the following fails:

换句话说，以下失败：

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";

---

---

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 PATHin the shell or include_pathin PHP, etc. You can check your current schema search path:

根据您的评论，您可以向“search_path”添加一个架构，这样当您引用表名而不限定其架构时，查询将通过按顺序检查每个架构来匹配该表名。就像PATH在 shell 或include_pathPHP 等中一样。您可以检查您当前的架构搜索路径：

SHOW search_path
  "$user",public

You can change your schema search path:

您可以更改架构搜索路径：

SET search_path TO showfinder,public;

See also http://www.postgresql.org/docs/8.3/static/ddl-schemas.html

另见http://www.postgresql.org/docs/8.3/static/ddl-schemas.html

回答by Mitzi

I had problems with this and this is the story (sad but true) :

我遇到了这个问题，这就是故事（悲伤但真实）：

1. If your table name is all lower case like : accounts you can use: select * from AcCounTsand it will work fine

2. If your table name is all lower case like : accountsThe following will fail: select * from "AcCounTs"

3. If your table name is mixed case like : AccountsThe following will fail: select * from accounts

4. If your table name is mixed case like : AccountsThe following will work OK: select * from "Accounts"

1. 如果你的表名都是小写的，比如：accounts you can use:select * from AcCounTs并且它会正常工作

2. 如果您的表名全部小写，例如：accounts以下将失败： select * from "AcCounTs"

3. 如果您的表名是大小写混合的，例如：Accounts以下将失败： select * from accounts

4. 如果您的表名是大小写混合的，例如：Accounts以下将正常工作： select * from "Accounts"

I dont like remembering useless stuff like this but you have to ;)

我不喜欢记住这样无用的东西，但你必须;)

回答by JarosPL

Put the dbname parameter in your connection string. It works for me while everything else failed.

将 dbname 参数放在连接字符串中。它对我有用，而其他一切都失败了。

Also when doing the select, specify the your_schema.your_tablelike this:

同样在进行选择时，指定your_schema. your_table像这样：

select * from my_schema.your_table

回答by Ugur Artun

Postgres process query different from other RDMS. Put schema name in double quote before your table name like this, "SCHEMA_NAME"."SF_Bands"

Postgres 处理查询不同于其他 RDMS。将模式名称放在您的表名之前的双引号中，例如“SCHEMA_NAME”.“SF_Bands”

回答by sav

I had a similar problem on OSX but tried to play around with double and single quotes. For your case, you could try something like this

我在 OSX 上遇到了类似的问题，但尝试使用双引号和单引号。对于你的情况，你可以尝试这样的事情

$query = 'SELECT * FROM "sf_bands"'; // NOTE: double quotes on "sf_Bands"

回答by Alexander Kuzichkin

This is realy helpfull

这真的很有帮助

SET search_path TO schema,public;

I digged this issues more, and found out about how to set this "search_path" by defoult for a new user in current database.

我更多地挖掘了这个问题，并发现了如何通过默认为当前数据库中的新用户设置这个“search_path”。

Open DataBase Properties then open Sheet "Variables" and simply add this variable for your user with actual value.

打开数据库属性，然后打开工作表“变量”，只需为您的用户添加具有实际值的变量。

So now your user will get this schema_name by defoult and you could use tableName without schemaName.

所以现在您的用户将默认获得这个 schema_name，您可以使用 tableName 而不使用 schemaName。

回答by kira

You must write schema name and table name in qutotation mark. As below:

您必须在引号中写入架构名称和表名称。如下：

select * from "schemaName"."tableName";

回答by ?zer S.

For me the problem was, that I had used a query to that particular table while Django was initialized. Of course it will then throw an error, because those tables did not exist. In my case, it was a get_or_createmethod within a admin.py file, that was executed whenever the software ran any kind of operation (in this case the migration). Hope that helps someone.

对我来说，问题是，我在 Django 初始化时使用了对该特定表的查询。当然它会抛出一个错误，因为那些表不存在。就我而言，它是get_or_createadmin.py 文件中的一个方法，只要软件运行任何类型的操作（在本例中为迁移），就会执行该方法。希望能帮助某人。

回答by Alexis Gamarra

You have to add the schema first e.g.

您必须先添加架构，例如

SELECT * FROM place.user_place;

If you don't want to add that in all queries then try this:

如果您不想在所有查询中添加它，请尝试以下操作：

SET search_path TO place;

Now it will works:

现在它将起作用：

SELECT * FROM user_place;

回答by meMadhav

Easiest workaround is Just change the table name and all column names to lowercaseand your issue will be resolved.

最简单的解决方法是将表名和所有列名更改为小写，您的问题将得到解决。

For example:

例如：

• Change Table_Nameto table_nameand
• Change ColumnNameto columnname

• 更改Table_Name为table_name和
• 更改ColumnName为columnname