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).
On Mar 11, 2:10 pm, Ross Peoples <[email protected]> wrote: > 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. > > On Mar 11, 2011, at 3:02 PM, Massimo Di Pierro wrote: > > > > > > > > > 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. > > > On Mar 11, 12:56 pm, Ross Peoples <[email protected]> wrote: > >> 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... > > >> On Thursday, March 10, 2011 3:02:25 PM UTC-5, Richard wrote: > > >>> 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 > > >>> On Thu, Mar 10, 2011 at 2:42 PM, Ross Peoples <[email protected]> wrote: > > >>>> 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"
