An here comes case (4) I have a table and data PostgreSQL, and I want to transfer them into Derby. DDL-Utils generates and creates a new table and then insert the data into the table. But the value of the autoincrement from Postgres is not passed through to Derby, hence an error at the next attempt to insert data in the table of the Derby Database. Of course, if the table was empty in Postgres there is no problem.
Cheers, [EMAIL PROTECTED] ----- Message d'origine ---- De : Thomas Dudziak <[EMAIL PROTECTED]> À : [email protected] Envoyé le : Samedi, 25 Novembre 2006, 1h30mn 18s Objet : Re: Identity start values (from Postgres to Derby) On 11/24/06, Laurent ROCHE <[EMAIL PROTECTED]> wrote: > I could not find anything about this issue on the website nor JIRA. Can the > mailing list archives be searched ? > > I am doing an export from a PostgreSQL database to a Derby database. > Some of my tables have one SERIAL colum (in Postgres) which is correctly > translated into Identity (in Derby) ... however the Identity value generated > will be always 1 as the tables are created like this: > CREATE TABLE my_table_auto(auto_id SMALLINT NOT NULL GENERATED BY DEFAULT AS > IDENTITY (START WITH 1, INCREMENT BY 1), ... > > I would expect DDL-Utils to generate something along the lines of: > ALTER TABLE my_table_auto ALTER COLUMN auto_id RESTART WITH > max_val_in_the_table > > Where of course max_val_in_the_table is the maximum plus 1 of the id of the > table > (i.e. SELECT MAX(auto_id) + 1 FROM my_table_auto) Sorry, I don't quite understand the issue here? AFAICS There are three different cases: (1) The table in Derby is new (i.e. CREATE TABLE). In this case, starting at 1 is perfectly fine and valid. (2) The table already exists in Derby and it has the same auto-increment settings. In this case, it makes no sense to reset the auto-increment counter because it should already be at the correct value (and if not, e.g. because of manual insertions, then you should perhaps not use auto-increment anyways). (3) The table already exists in Derby but it does not have auto-increment specified. If the table already has data in it, you should perhaps not use auto-increment (it depends on how the data was put into the table; e.g. if a different program also uses the table, then you might break it with making the column autoincrement). If it does not have data in it, then starting at 1 would be fine. cheers, Tom ___________________________________________________________________________ Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions ! Profitez des connaissances, des opinions et des expériences des internautes sur Yahoo! Questions/Réponses http://fr.answers.yahoo.com
