views:

3181

answers:

2

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  A: 

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
Oops, forgive me. I meant to say that my table name has no uppercase letters, not my database name.
Keyslinger
That did it, many thanks!
Keyslinger
Right on the money! +1
Nippysaurus
A: 

pg_query($dbconn, $query)

make sure the database connection is successful...

nooneinparticular
this is a nonsense, if the connection fail the code he posted would stop at `or die('Could not connect: ' . pg_last_error());`
DaNieL