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 Laurent ROCHE <la...@yahoo.com> on 2006/11/24 19:00:07 UTC
Identity start values (from Postgres to Derby)
Hi All,
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)
Am I missing something ? Is this not supported ?
Have fun,
L@u
The Travelling Froggy
TravellingFroggy.info
___________________________________________________________________________
Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.
http://fr.mail.yahoo.com
Re: Identity start values (from Postgres to Derby)
Posted by Thomas Dudziak <to...@gmail.com>.
On 11/24/06, Laurent ROCHE <la...@yahoo.com> 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