You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Igor Markovic <ig...@izecom.com> on 2006/12/06 16:33:08 UTC
Fw: Identity start values
I also have the same issue. Here a test schema and some data that wil cause this problem:
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="MigrateTest">
<table name="log">
<column name="id" primaryKey="true" required="true" type="INTEGER" size="4" autoIncrement="true"/>
<column name="entry_date" primaryKey="false" required="true" type="TIMESTAMP" size="8,6" autoIncrement="false"/>
<column name="hostname" primaryKey="false" required="true" type="VARCHAR" size="255" autoIncrement="false"/>
<column name="process" primaryKey="false" required="true" type="VARCHAR" size="255" autoIncrement="false"/>
</table>
</database>
and the following data:
<?xml version="1.0" encoding="utf-8"?>
<data>
<log id="1" entry_date="2006-12-03 02:01:33.9" hostname="somehost" process="test9"/>
<log id="2" entry_date="2006-12-03 02:01:33.8" hostname="somehost" process="test8"/>
<log id="3" entry_date="2006-12-03 02:01:33.7" hostname="somehost" process="test7"/>
<log id="4" entry_date="2006-12-03 02:01:33.6" hostname="somehost" process="test6"/>
<log id="5" entry_date="2006-12-03 02:01:33.5" hostname="somehost" process="test5"/>
<log id="6" entry_date="2006-12-03 02:01:33.4" hostname="somehost" process="test4"/>
<log id="7" entry_date="2006-12-03 02:01:33.3" hostname="somehost" process="test3"/>
<log id="8" entry_date="2006-12-03 02:01:33.2" hostname="somehost" process="test2"/>
<log id="9" entry_date="2006-12-03 02:01:32.1" hostname="somehost" process="test1"/>
<log id="10" entry_date="2006-12-03 02:01:32.0" hostname="somehost" process="test0"/>
</data>
Now the problem is when I import the data again, the sequence for the id column is created again, but it is left on the default position (1). So when I insert a new record, the record is inserted with id 1 instead of 11 and causes an unique constraint violation. To test this you could insert a record by using the syntax:
INSERT INTO log (entry_date,hostname,process) VALUES ({ts '2006-12-03 02:01:32'},'somehost','test99');
To set the sequence in Postgres you can use:
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
or with
SELECT setval('log_id_seq', value);
I don't know exactly how this is done in other databases. If you now try to insert the record it should work again.
I hope someone can fix this for me, cause it's one of the last problems I have with Ddlutils I would have to solve.
Igor