Option to exclude individual columns about ora2pg HOT 9 CLOSED

There's already something like that, see MODIFY_STRUCT in configuration file.

For example if you have a table in Oracle named table1 with 3 columns named col1, col2 and col3 and you just want to export data from col1 and col3:

MODIFY_STRUCT  TABLE1(col1,col3)

It doesn't matter if the col2 column exist or not in the destination table.

You can set a list of such modified tables separated by space.

Regards,

from ora2pg.

According to the documentation this only works for data export - not ddl export?

The unused columns might have an NOT NULL so I can't read the data into postgresql if a column i striped from the data but not ddl.

Another question: can this option be used to rearrange the order of columns in postgresql? I learned during migration that I get a more efficient storage if small columns (like boolean columns) are next to each other.

So can I rearrange an oracle table like mytable(number(19,0) c1, number(1,0) c2, varchar2(3) c3, number(1,0) c4) into a postgresql table like mytable(integer c1, varchar(3) c3, boolean c2, boolean c4) with MODIFY_STRUCT mytable(c1, c3, c2, c4)?

from ora2pg.

When I said "It doesn't matter if the col2 column exist or not in the destination table.", obviously, this require that the column doesn't have a NOT NULL constraint.

This directive is only used for data export because it is very simple to add an "alter table ... drop column ..." after your schema import. There's no need to include that into ora2pg.

No, there's no option to rearrange columns order in postgresql, but this can be a new feature. I will see if that can be done easily. Using MODIFY_STRUCT you can just specify the columns that will be used to export/import data.

from ora2pg.

Hi Christian,

The feature of reordering columns following alignment is now included in last release v12.0 that was just published today. It can be downloaded at https://sourceforge.net/projects/ora2pg/.

Thanks for the feature request.

Best regards,

from ora2pg.

So in total there is no way to exclude columns? I am using TABLE migration type and when I deifned:

MODIFY_STRUCT my_table(col1, col3)

The col2 is still here after migration. So only reordering is supported?

P.S. ora2pg is v18.1

from ora2pg.

The MODIFY_STRUCT is only available with data export to adapt to any schema transformation. But I think that with some additional work it could be available for tables/indexes/constraints export.

from ora2pg.

So could I create new task for this or reopen current task?

from ora2pg.

I've reopen it, thanks.

from ora2pg.

Commit 2315983 allow use of MODIFY_STRUCT with TABLE export. Table creation DDL will respect the new list of columns and all indexes or foreign key pointing to or from a column removed will not be exported.

Another question: can this option be used to rearrange the order of columns in postgresql? I learned during migration that I get a more efficient storage if small columns (like boolean columns) are next to each other.

I've forgotten to answer to this question, for automatic reordering by ora2pg you just have to enable the REORDERING_COLUMNS configuration directive in ora2pg.conf. It must be a bit too late, but can be useful for later use and other users.

from ora2pg.