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.
