Re : Identity start values (from Postgres to Derby)

[Laurent ROCHE <la...@yahoo.com>]

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,
L@u


----- Message d'origine ----
De : Thomas Dudziak <to...@gmail.com>
À : ddlutils-user@db.apache.org
Envoyé le : Samedi, 25 Novembre 2006, 1h30mn 18s
Objet : Re: Identity start values (from Postgres to Derby)

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





	

	
		
___________________________________________________________________________ 
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

Re: Re : Identity start values (from Postgres to Derby)

[Thomas Dudziak <to...@gmail.com>]

On 11/24/06, Laurent ROCHE <la...@yahoo.com> wrote:

> 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.

Ah now, that's something different. I assumed that the Derby table was
a problem. But this sounds like a bug, so please file an issue in JIRA
and attach (if possible) SQL for the PostgreSql table.

thanks,
Tom