From: Robert Klemme <shortcutter@...>
Date: 2012-09-18T16:45:17+09:00
Subject: Re: Ruby Postgres driver (pg) and case sensitivity

On Tue, Sep 18, 2012 at 9:02 AM, Thomas Bednarz <lists@ruby-forum.com> wrote:
> 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!

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

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/