db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Igor Markovic" <igor-l...@izecom.com>
Subject Fw: Identity start values
Date Wed, 06 Dec 2006 15:33:08 GMT
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
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message