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
