Ruby Postgres driver (pg) and case sensitivity

Ruby
1

I am playing around with ruby and postgres. Postgresql is usually cAsE
SenSiTive. When I create a prepared statement in ruby like

conn = PGconn.open(…)
conn.prepare(‘stmt1’, ‘insert into MyTable(my_number, my_string)
values($1, $2)’
conn.exec_prepared(‘stmt1’, [@number, @string])

I get an error that mytable was not found! (the tablename is now all
lowercase!)

Does the pg ruby driver automatically convert all table names to
lowercase?? Is there any way to configure this behaviour? If you access
legacy databases there is no way to influence their naming conventions!

Thanks for any hints.
Tom

2

On Tue, Sep 18, 2012 at 9:02 AM, Thomas B. [email protected]
wrote:

Does the pg ruby driver automatically convert all table names to
lowercase?? Is there any way to configure this behaviour? If you access
legacy databases there is no way to influence their naming conventions!

It is common with SQL databases that table names given are case
insensitive.

$ psql
psql (9.1.5)
Type “help” for help.

rklemme=> create table ta ( val numeric(4) not null );
CREATE TABLE
rklemme=> insert into ta values ( 1 );
INSERT 0 1
rklemme=> create table “Ta” ( val numeric(4) not null );
CREATE TABLE
rklemme=> insert into “Ta” values ( 2 );
INSERT 0 1
rklemme=> select * from ta;
val

1
(1 row)

rklemme=> select * from Ta;
val

1
(1 row)

rklemme=> select * from TA;
val

1
(1 row)

rklemme=> select * from tA;
val

1
(1 row)

rklemme=> select * from “Ta”;
val

2
(1 row)
rklemme=> \d
List of relations
Schema | Name | Type | Owner
--------±-------------------------±---------±--------
public | Ta | table | rklemme
public | ta | table | rklemme

Maybe you can do this:

conn.prepare(‘stmt1’, ‘insert into “MyTable”(my_number, my_string)
values($1, $2)’

Kind regards

robert

3

Hi Robert,

Well the postgres server is not the problem, I use pgAdmin with its ISQL
to execute statements and there case doesn’t matter either.

I had the problem with the Ruby Driver, but I will extend my test
programm a little to see, if this is really true in various situations
(updates, deletes, inserts and calls of stored procedures).

Tom

4

On Tue, Sep 18, 2012 at 1:47 PM, Thomas B. [email protected]
wrote:

Well the postgres server is not the problem, I use pgAdmin with its ISQL
to execute statements and there case doesn’t matter either.

I know. I used psql to illustrate the point about case handling
because I assume that it might be similar with pg driver. Apparently
that wasn’t clear to you.

I had the problem with the Ruby Driver, but I will extend my test
programm a little to see, if this is really true in various situations
(updates, deletes, inserts and calls of stored procedures).

Why don’t you just test my suggestion?

robert