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"
