Your source table defines id as an integer. Your target table defines id
as a string with length 8.
Therefore it will not auto increment. Suggest you look at the manual for
your target database to see how to do an auto increment integer for primary
key.
On Monday, June 10, 2013 11:55:32 AM UTC-4, lesssugar wrote:
>
> 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.
