I had built a small application using Postgresql as my database. I used the phpgpadmin to create my database schema (if I remember correctly). When I tried to issue SQL statement I always received errors that the tables didn’t exist, I figured out that if I used “TABLE”, then it worked.
Yesterday evening I had enough. I started googling why this was happening. After some time I found the answer and doing some testcases, it seems that this behaviour was pretty logical.
In short if you use Postgresql
create table TeSt(iD varchar(200));
Is not the same as
create table "TeSt"("iD" varchar(200));
If you do the first you can actually perform select id from test (or select Id from tEsT (reverse capitals). I get the feeling everything is converted to lowercase!
But if you issued the second statement the former selects won’t work. You really have to make it pinpoint accurate. So any people out there giving Postgresql a go. Take care how you generate your tables!!
The fix I did was pretty straightforward. Export my database to a sql file (using phppgadmin)(Also export data!). Then open with your favorite editor and replace all ” with nothing, save. Drop all your tables (but be sure you exported also the data 🙂 ). After you schema is empty, perform the SQL and you should be up and running without the case sensitive stuff.
NOTE
I do agree that TeSt is not the same as test. I’m pro linux filesystem file conventions, but for sql it really is a pain in the ass when you use generator tools and they fuck up.