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

Keyslinger_80857 0
Viewed 643790

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?

quoted-identifier postgresql sql php
17 Answers

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

Bill_Karwin_208600

If a table name contains underscores or upper case, you need to surround it in double-quotes.

SELECT * from "Table_Name";
user_24803620

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

select * from "schemaName"."tableName";
user_57367940

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.

user_96554670

I had the same issue as above and I am using PostgreSQL 10.5. I tried everything as above but nothing seems to be working.

Then I closed the pgadmin and opened a session for the PSQL terminal. Logged into the PSQL and connected to the database and schema respectively :

\c <DATABASE_NAME>;
set search_path to <SCHEMA_NAME>;

Then, restarted the pgadmin console and then I was able to work without issue in the query-tool of the pagadmin.

user_42354690

In addition to Bill Karwin's answer =>

Yes, you should surround the table name with double quotes. However, be aware that most probably php will not allow you to just write simply:

$query = "SELECT * FROM "SF_Bands"";

Instead, you should use single quotes while surrounding the query as sav said.

$query = 'SELECT * FROM "SF_Bands"';
user_107956050

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

For example:

  • Change Table_Name to table_name and
  • Change ColumnName to columnname
user_103204360

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

It might be silly for a few, but in my case - once I created the table I could able to query the table on the same session, but if I relogin with new session table does not exits.

Then I used commit just after creating the table and now I could able to find and query the table in the new session as well. Like this:

select * from my_schema.my_tbl;

Hope this would help a few.

user_29877800

Make sure that Table name doesn't contain any trailing whitespaces

enter image description here

user_144299200

Try this: SCHEMA_NAME.TABLE_NAME

user_195708240

I tried every good answer ( upvote > 10) but not works.

I met this problem in pgAdmin4.

so my solution is quite simple:

  1. find the target table / scheme.

  2. mouse right click, and click: query-tool

  3. in this new query tool window, you can run your SQL without specifying set search_path to <SCHEMA_NAME>; enter image description here

  4. you can see the result: enter image description here

user_4459080
Related