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

Question

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?

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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"

View more solutions

Comments

[brian-brazil]

Are you sure that the sf_bands table exists? Does showfinder.sf_bands work?

[Keyslinger]

showfinder.sf_bands works perfectly

[Keyslinger]

Perhaps I should note that my database was migrated from MySQL

[brian-brazil]

Can you try pg_query($dbconn, $query)? The implicit connection can cause hard-to-debug issues, may as well eliminate it as a possible problem. Can you also try pg_dbname($dbconn) to make sure it's indeed connected to showfinder?

[Brain2000]

+1 for mentioning that the uppercase letters are the problem. I spent an hour trying to figure out why I could not select from a single table in PostgreSQL. What a terrible program.

[Keyslinger]

Oops, forgive me. I meant to say that my table name has no uppercase letters, not my database name.

[Roman Starkov]

It appears that even if you type SELECT * FROM SF_Bands this will still fail, because Postgres decides to lowercase that table name for you. Weird...

[Bill Karwin]

@romkyns: Yes, this is actually pretty common across RDBMS brands, that undelimited identifiers are advertised as "case-insensitive." But they're not truly case insensitive because the way they've implemented that is to force lowercase. This matches the name of the table only if you had allowed the table name to be lowercased when you defined the table. If you use double-quote delimiters when you CREATE TABLE, you must use delimiters when you reference it in queries.

[Yaroslav]

What does your answer adds to the previously accepted answer, upvoted 22 times and with lot of details?

[Roland]

Same for column names in where-clauses

[Roland]

5. Mixed case, like Accounts, will fail with select * from Accounts; I find the weirdest part: same-case is NOT identical.

[JoeTidee]

Putting the schema name in, e.g. my_schema.my_relation into the query helped.

[Erndob]

All there is to it: all names in postgres query are lowercase, unless you use quotes.

[Charlotte]

Thank you very much! It reall helps me solve the problem! But is there a way that i can omit the scheme name?

[Andy]

Postgres automatically lowercases table names if they aren't in quotes? That's pretty asinine...

[Bill Karwin]

@Andy, when you write your own SQL database, feel free to implement case-insensitive identifiers some other way. :)

[Andy]

@BillKarwin Really, Postgres should be courageous enough to release more sensible, modern case handling as a breaking change.

[Laurenz Albe]

Before you heap ill-guided invective on PostgreSQL, listen to what the SQL standard has to say: An <SQL language identifier> is equivalent to an <SQL language identifier> in which every letter that is a lower-case letter is replaced by the corresponding upper-case letter or letters. This treatment includes determination of equivalence, representation in the Information and Definition Schemas, representation in diagnostics areas, and similar uses. So while PostgreSQL does not follow the standard in that it folds everything to lower case, case insensitivity as such is required by the standard.

[Muli]

The fourth option worked for me, though I'm not using PHP

[Tashows]

Also make sure you are connected to the right db. Keyslinger was doing that right, but there are other workflows that might confuse people or make them oversee the database part.

[GetHacked]

Thanks for laying out all the interactions! :)

[jrh]

On the off chance somebody else runs into this, I used camelCase accidentally in a Django ManyToManyField, which caused the migration to create a table of mysterious casing, which shows as lowercase in both pgadmin and psql, but SELECT * from mytable only worked as SELECT * from "mytable". I'm not really sure how to figure out what casing it actually is.

[visconttig]

Thanks! ___________

[Irfan Khan]

In my case , I had written the table name in uppercase but it was in lowercase, thanks a lot . It saved a lot of my time.

[Asad S]

thankyou. i was worried i messed up the db.