Good to know, thanks.
W dniu poniedziałek, 10 czerwca 2013 17:35:04 UTC+2 użytkownik Niphlod
napisał:
>
> sorry to interrupt your monologue but, BTW, web2py's table definition has
> no notion of compound unique indexes, so the only way to enforce them (if
> you need those) is to alter the table manually (as you did).
>
> Il giorno lunedì 10 giugno 2013 14:51:05 UTC+2, lesssugar ha scritto:
>>
>> OK, found a way around. I executed the following commands in postgres and
>> the CSV file imported correctly:
>>
>> ALTER TABLE city DROP CONSTRAINT city_country_key;
>> ALTER TABLE city ADD CONSTRAINT city_country_key UNIQUE (name, country,region
>> );
>>
>> Cheers.
>>
>> On Monday, June 10, 2013 1:36:47 PM UTC+2, lesssugar wrote:
>>>
>>> Further with my monolog... Seems I need to implement this im my table
>>> definition:
>>>
>>> UNIQUE KEY `country` USING BTREE (`country`,`name`,`region`)
>>>
>>> How do I do it in web2py?
>>>
>>>
>>> On Monday, June 10, 2013 1:24:20 PM UTC+2, lesssugar wrote:
>>>>
>>>> Yeah, the question is: how 'country' can be a UNIQUE KEY if countries
>>>> have hundreds of cities. In the table, hundreds of cities will belong to
>>>> the same country, so the no wonder the contry code (which is two letters)
>>>> will not be unique. I'm confused.
>>>>
>>>> On Monday, June 10, 2013 12:59:26 PM UTC+2, lesssugar wrote:
>>>>>
>>>>> OK, I'm starting to think it's a wrongly defined table. The table I
>>>>> would like to import has following structure:
>>>>>
>>>>> CREATE TABLE `cities` (
>>>>> `ID` int(8) unsigned NOT NULL auto_increment,
>>>>> `country` char(2) NOT NULL,
>>>>> `region` char(3) NOT NULL,
>>>>> `url` varchar(50) NOT NULL,
>>>>> `name` varchar(50) NOT NULL,
>>>>> `latitude` double NOT NULL,
>>>>> `longitude` double NOT NULL,
>>>>> PRIMARY KEY (`ID`),
>>>>> UNIQUE KEY `country` USING BTREE (`country`,`name`,`region`)
>>>>> ) ENGINE=InnoDB AUTO_INCREMENT=207637 DEFAULT CHARSET=utf8;
>>>>>
>>>>> My 'city' table definition looks like this:
>>>>>
>>>>> db.define_table('city',
>>>>> Field('id', length=8),
>>>>> Field('country', length=2, unique=True),
>>>>> Field('region', length=3),
>>>>> Field('url', length=50),
>>>>> Field('name', length=50),
>>>>> Field('latitude', 'double'),
>>>>> Field('longitude', 'double'),
>>>>> format=lambda r: r.name
>>>>> )
>>>>>
>>>>> Does someone see an error here?
>>>>>
>>>>> On Monday, June 10, 2013 12:35:23 AM UTC+2, lesssugar wrote:
>>>>>>
>>>>>> I'm aware this might be rather a postgresql question but maybe
>>>>>> someone knows how to deal with it.
>>>>>>
>>>>>> I defined 'city' table
>>>>>>
>>>>>> db.define_table('city',
>>>>>> Field('id', length=8),
>>>>>> Field('country', length=2, unique=True),
>>>>>> Field('region', length=3),
>>>>>> Field('url', length=50),
>>>>>> Field('name', length=50),
>>>>>> Field('latitude', 'double'),
>>>>>> Field('longitude', 'double'),
>>>>>> format=lambda r: r.name
>>>>>> )
>>>>>>
>>>>>> The table is empty and I'm trying to import data from CSV file using
>>>>>> web2py's appadmin (the CSV provides fixed IDs).
>>>>>>
>>>>>> Every time I import the file I get the following error:
>>>>>>
>>>>>> *duplicate key value violates unique constraint "city_country_key"'*
>>>>>> *
>>>>>> *
>>>>>> I found many similar problems on the net and one of the reasons
>>>>>> causing this error was lack of sync between (in my case) 'city' and
>>>>>> 'city_id_seq'. However the sync seems to be OK, because:
>>>>>>
>>>>>> A. select max(id) from city; -- returns nothing (the table 'city' is
>>>>>> empty as I wrote)
>>>>>> B. select nextval('city_id_seq'); -- returns an integer value
>>>>>>
>>>>>> So the result of B. > the result of A. which would suggest it's not a
>>>>>> sync problem. I've been fighting with this all day. If someone knows
>>>>>> what's
>>>>>> going on, please let me know.
>>>>>>
>>>>>>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
