Friday, February 09, 2007

Postgres Double Quotes

I'm working a new project and i'm using postgres for the first time in a while...

I was experiencing very strange behavior, queries are only working if i double quote the column and table names.

I know this from autogenerating the query in pgAdmin III. I'm using CFMX and JDBC, but as always with a database, go back to the native db tools first to test a
problem

SELECT "USER_ID", "EMAIL_ADDRESS", "FIRST_NAME",
FROM "USERS";

works fine!

SELECT USER_ID, USER_TYPE_ID, EMAIL_ADDRESS, FIRST_NAME, MIDDLE_NAME,
LAST_NAME, USER_PASSWORD, DATE_CREATED, DATE_UPDATED,
DATE_LAST_LOGIN, DATE_PASSWORD, IS_ACTIVE
FROM USERS;

but this one fails with "ERROR: relation "users" does not exist"

I had been stumped for a while and i then I found the answer: http://www.thescripts.com/forum/thread3102.html

The create table script had double quotes around all the column and table names and will only work as such as a result, "USERS" and USERS are two different things..

very intuitive... it's friday

1 comment:

Anonymous said...

Thanks. Was getting very annoyed with postgres till I read your post.