Legacy PostgreSQL Database using case sensitive table and field names

[Ross Peoples]

I have a legacy PostgreSQL database that has its tables and field names created using a case-sensitive means. Whenever I try to do a select(), it returns no rows, and an insert() fails. This is the error that web2py gives:

ProgrammingError: relation "globalsettings_id_seq" does not exist
LINE 1: select currval('GlobalSettings_id_Seq')

This is the actual table definition from pgAdmin3:

CREATE TABLE "GlobalSettings"

(

  "settingName" character varying(255) NOT NULL,

  "settingValue" text NOT NULL,

  "settingID" serial NOT NULL,

  CONSTRAINT "GlobalSettings_pkey" PRIMARY KEY ("settingID")

)

Attempting to define my table in web2py using double-quotes between single-quotes, like this:

db.define_table('"GlobalSettings"',

    Field('"settingName"', length=255, unique=True),

    Field('"settingValue"', 'text'),

    Field('"settingID"', 'id')

)

results in the following error message: SyntaxError: only [0-9a-zA-Z_] allowed in table and field names, received "settingName"

[Richard Vézina]

remove the " from web2py model and add the sequence name that postgres create by default with a different name then web2py expect like this :

db.define_table('GlobalSettings',

    Field('settingName', length=255, unique=True),

    Field('settingValue', 'text'),

    Field('settingID', 'id'),

    sequence_name='GlobalSettings_settingID_seq'

)

It should works...

Richard

[Ross Peoples]

After also adding migrate=False to the table definition, I now get this error:

ProgrammingError: relation "globalsettings" does not exist

LINE 1: ...ngs.settingName, GlobalSettings.settingValue FROM GlobalSett...

[Massimo Di Pierro]

It cannot be

Field('"settingName"', length=255, unique=True),

must be

Field('settingName', length=255, unique=True),

you cannot have quotes in the file name.
So it should be

db.define_table('GlobalSettings',
Field('settingName', length=255, unique=True),
Field('settingValue', 'text'),
Field('settingID', 'id'),

sequence_name='GlobalSettings_settingID_seq',
migrate=False)

Case must match DB. migrate=False because it exists. I am not 100%
sure but I think this should work.

[Ross Peoples]

I should have mentioned that I already did this, so now my table definition matches the one you gave. It was after correcting the definition that I get this message. Again, the tables and fields were created using double-quotes (e.g. CREATE TABLE "GlobalSettings"...), so PostgreSQL requires that all queries use double-quotes when referring to tables and fields created this way.

If I do a db()._select(db.GlobalSettings.ALL)...notice the underscore, I get:

SELECT GlobalSettings.settingID, GlobalSettings.settingName, GlobalSettings.settingValue FROM GlobalSettings;

But for PostgreSQL to accept the query, it would have to be:

SELECT "GlobalSettings"."settingID", "GlobalSettings"."settingName", "GlobalSettings"."settingValue" FROM "GlobalSettings";

This is because unless you specifically wrap table and field names with double-quotes, PostgreSQL automatically converts all table and field names to lowercase.

[Massimo Di Pierro]

Then web2py cannot do this (yet). You would need to create a view for
your tables with lowercase table names or perhaps there is some
obscure postgresql parameters to make the queries case sensitive
(mysql has it).

[pbreit]

Note to self: never use capitals!

[Ross Peoples]

I couldn't find anything in the PG docs. If I were to add another postgres adapter that specifically handles databases created like this, is that something you'd be interested in adding to web2py?

On Friday, March 11, 2011 4:42:11 PM UTC-5, Massimo Di Pierro wrote:

Then web2py cannot do this (yet). You would need to create a view for
your tables with lowercase table names or perhaps there is some
obscure postgresql parameters to make the queries case sensitive
(mysql has it).