douqiang1910
2009-03-29 20:16 阅读 210
已采纳

不能简单地使用PostgreSQL表名(“关系不存在”)

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

This produces the following error:

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

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

8条回答 默认 最新

  • 已采纳
    dsfdsfdsfdsf1223 dsfdsfdsfdsf1223 2009-03-29 20:25

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

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

    点赞 评论 复制链接分享
  • douyunhuan9886 douyunhuan9886 2011-03-03 18:38

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

    Also when doing the select, specify the your_schema.your_table like this:

    select * from my_schema.your_table
    
    点赞 评论 复制链接分享
  • doudao1922 doudao1922 2012-07-10 20:10

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

    点赞 评论 复制链接分享
  • dongzanghua8422 dongzanghua8422 2012-09-03 15:35

    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 AcCounTs and it will work fine

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

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

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

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

    点赞 评论 复制链接分享
  • douguxun6866 douguxun6866 2015-02-05 14:13

    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

    $query = 'SELECT * FROM "sf_bands"'; // NOTE: double quotes on "sf_Bands"
    
    点赞 评论 复制链接分享
  • dongpu3347 dongpu3347 2016-04-05 15:30

    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_create method 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.

    点赞 评论 复制链接分享
  • dongshanyan0322 dongshanyan0322 2018-06-16 07:29

    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.

    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.

    点赞 评论 复制链接分享
  • dqp10099 dqp10099 2019-09-06 07:38

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

    select * from "schemaName"."tableName";
    
    点赞 评论 复制链接分享