Loading...

ddlutils-user@db.apache.org

[Prev] Thread [Next]  |  [Prev] Date [Next]

Re : Identity start values (from Postgres to Derby) Laurent ROCHE Fri Nov 24 18:06:20 2006

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