PostgreSQL case sensitive fields

[Kostis Mentzelos]

Hi all,

I am using PostgreSQL database and I have some tables created with case sensitive fields like:

create table "TestTable"

(

"eKey" text,

"eValue" text

)

looking other messages I found some topics talking about case sensitive table names which is fixed but it is not fixed for select or insert

error:

Oops: QueueServer:org.postgresql.util.PSQLException: ERROR: relation "testtable" does not exist

  Position: 13, Query: INSERT INTO TestTable (ekey, evalue) VALUES (?, ?), params: HIST,auto message

in order to work this query should be:

INSERT INTO "TestTable" ("eKey", "eValue") VALUES (?, ?), params: HIST,auto message

how to fix this?

[Igor Polevoy]

Kostis, this is something we ddi not foresee when developing AJ, and it
comes back and bites us in the ass one in a while - usually when people
deal with PostgreSQL
Suggestions are:

1. Rename all tables and columns to be lower case
2. Disable case sensitivity in PostgreSQL
3. Dig into code and send in a pull request with the fix.

I think 1 or 2 will make more sense for you.

Thanks
Igor

> --
> You received this message because you are subscribed to the Google
> Groups "ActiveJDBC Group" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to activejdbc-gro...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Kostis Mentzelos]

yes I understand that. 

renaming all existing tables is a very difficult task because the database is accessed by other applications (borland delphi zeos). it is a working environment with a lot of tables. 

disabling case sensitivity is something that I don't know if it is possible in postgres. do you know how?

[Igor Polevoy]

no, I do not, sorry

[Kostis Mentzelos]

what do I need to dig into code? 

[Igor Polevoy]

Fork this:
https://github.com/javalite/activejdbc

look at https://github.com/javalite/activejdbc/blob/master/build_postgresql.sh

get this running locally for you, and look for toLowerCase() methods across the code.
After you make the fix, send in a pull request, we will test across all other databases, and if works, we will make a new release.

thanks

[Kostis Mentzelos]

I need some help here to get this running localy. I have created an account and fork the project. how can I get the source and build it localy?

[Igor Polevoy]

you need to fork the code to create your local repo - there is plenty of Git related articles to do  this on the Internet.

for building, look at build_postgresql.sh script

thanks

[Kostis Mentzelos]

Hi Igor,
I am still strangling with case sensitive tables in PostgreSQL and ActiveJDBC.
I have read all threads, I have download the latest version but it is not working.
this problem is very difficult and I believe that @Table ("\"HWDATA\"") is not a good approach because one table can contain both case sensitive AND case insensitive columns.
It seems to me that everything should be case insensitive except the values that the JDBC driver returns in mix (upper and lower) case. These names ActiveJDBC should put inside double quotes.
what do you think?

[Igor Polevoy]

Hi, Kostis. 

There was some work done with names of tables and attributes. Can you please pull the latest released version and see if it solves your proboem. 

Thanks