SQL Postgresql 表存在，但查询时得到“关系不存在”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36753568/
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
Postgresql tables exists, but getting "relation does not exist" when querying
提问by patkil
I have a postgresql db with a number of tables. If I query:
我有一个带有许多表的 postgresql 数据库。如果我查询：
SELECT column_name
FROM information_schema.columns
WHERE table_name="my_table";
I will get a list of the columns returned properly.
我会得到一个正确返回的列的列表。
However, when I query:
但是，当我查询时：
SELECT *
FROM "my_table";
I get the error:
我收到错误：
(ProgrammingError) relation "my_table" does not exist
'SELECT *\n FROM "my_table"\n' {}
Any thoughts on why I can get the columns, but can't query the table? Goal is to be able to query the table.
关于为什么我可以获得列但无法查询表的任何想法？目标是能够查询表。
回答by Juan Carlos Oropeza
You have to include the schema if isnt a public one
如果不是公共架构，则必须包含架构
SELECT *
FROM <schema>."my_table"
Or you can change your default schema
或者您可以更改默认架构
SHOW search_path;
SET search_path TO my_schema;
Check your table schema here
在此处检查您的表架构
SELECT *
FROM information_schema.columns
For example if a table is on the default schema publicboth this will works ok
例如，如果一个表在默认架构上public，这都可以正常工作
SELECT * FROM parroquias_region
SELECT * FROM public.parroquias_region
But sectors need specify the schema
但部门需要指定架构
SELECT * FROM map_update.sectores_point
回答by Richie Rizal Amir
You can try:
你可以试试：
SELECT *
FROM public."my_table"
Don't forget double quotes near my_table.
不要忘记 my_table 附近的双引号。
回答by dfrankow
I had to include double quotes with the table name.
我必须在表名中包含双引号。
db=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------------------------------------------+-------+-------
public | COMMONDATA_NWCG_AGENCIES | table | dan
...
db=> \d COMMONDATA_NWCG_AGENCIES
Did not find any relation named "COMMONDATA_NWCG_AGENCIES".
???
？？？
Double quotes:
双引号：
db=> \d "COMMONDATA_NWCG_AGENCIES"
Table "public.COMMONDATA_NWCG_AGENCIES"
Column | Type | Collation | Nullable | Default
--------------------------+-----------------------------+-----------+----------+---------
ID | integer | | not null |
...
Lots and lots of double quotes:
很多很多双引号：
db=> select ID from COMMONDATA_NWCG_AGENCIES limit 1;
ERROR: relation "commondata_nwcg_agencies" does not exist
LINE 1: select ID from COMMONDATA_NWCG_AGENCIES limit 1;
^
db=> select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
ERROR: column "id" does not exist
LINE 1: select ID from "COMMONDATA_NWCG_AGENCIES" limit 1;
^
db=> select "ID" from "COMMONDATA_NWCG_AGENCIES" limit 1;
ID
----
1
(1 row)
This is postgres 11. The CREATE TABLE statements from this dump had double quotes as well:
这是 postgres 11。来自这个转储的 CREATE TABLE 语句也有双引号：
DROP TABLE IF EXISTS "COMMONDATA_NWCG_AGENCIES";
CREATE TABLE "COMMONDATA_NWCG_AGENCIES" (
...
回答by dmigwi
I had the same problem that occurred after I restored data from a postgres dumped db.
从 postgres 转储的数据库恢复数据后，我遇到了同样的问题。
My dump file had the command below from where things started going south.
我的转储文件有下面的命令，从那里开始向南。
SELECT pg_catalog.set_config('search_path', '', false);
Solutions:
解决方案：
- Probably remove it or change that
falseto betrue. - Create a private schema that will be used to access all the tables.
- 可能将其删除或将其更改
false为true. - 创建将用于访问所有表的私有模式。
The command above simply deactivates all the publicly accessible schemas.
上面的命令只是停用所有可公开访问的模式。
Check more on the documentation here: https://www.postgresql.org/docs/9.3/ecpg-connect.html
在此处查看文档的更多信息：https: //www.postgresql.org/docs/9.3/ecpg-connect.html
