Escape Keys - TomdeMan's Blog
Postgre Error: Relation does not exist

Author: TomdeMan
Related Categories: Postgre, Transfer, ColdFusion

July 31, 2007

I'm working on an app that integrates with the Transfer ORM. The code runs on an MS SQL and MySQL database without a problem. However, when switching over to Postgre one table causes things to flop.

When it queries the table i get the error: relation "tableName" does not exist.

I look at the SQL involved and its a basic select. So I try select * tableName, and get the same error in the query window.

If I add double quotes around "tableName" it works fine.

That's a solution but not an explanation for the problem.

Why is it, that for this tableName, I need the double quotes?

Well it can be one of two things...

The tableName is a reserved word in Postgre or when you created the table you used mixed cases (tableName) or upper case (TABLENAME). Had you created the table lower case (tablename) then you would have never seen this error. So if you are using an ORM that extends itself to Postgre be sure to create tables and column names in lower case.

  Comments (1)   |     Print   |     Send   |     del.icio.us  |     Digg It!  |     Linking Blogs  |   962 Views

Comments:

[Add Comment]

Brian says:

This happens if your CREATE TABLE statement has the table name in quotes (common in MySQL and others) like:

CREATE TABLE "MixedCase"

If you do this:

CREATE TABLE MixedCase

Then it will create it without any case sensitive issues. This kind of annoys me about Postgres but it's not a big deal given how awesome Postgres is otherwise.

8/20/08 1:46 PM

[Add Comment]