Re: MS SQL Server migration

> -----Original Message-----
> From: Roman Fail [mailto:rfail@posportal.com]
> Sent: 15 January 2003 18:59
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] MS SQL Server migration
>
>
> I recently completed a data migration to Postgres 7.3.1 using
> the pgAdminII Migration Wizard.  It is awesome, and so much
> easier to use than MS DTS.  Thanks for such a great tool.

Thanks.

> However, I was unable to migrate a table with a VARBINARY
> column using the wizard.  MS DTS was able to migrate the
> binary table to postgres just fine, but had memory problems
> due to the size of the table (23 million tuples).  So I ended
> up using bcp and a writing a hex2octal UDF to get the thing
> imported.  (And thanks to Ian Harding for a lot of good ideas
> in his tech doc).
>
> Is binary migration going to be supported at some point?
> Clearly ODBC can do it, I'm guessing it's just a matter of
> writing the routines.

Hmm, the next version of pgAdmin is being written cross-platform in C++.
I have far less spare time these days and as the Migration Wizard is
something I never use, it goes to the bottom of my personnal list I'm
afraid. Even when we do write it, it will likely be very different from
what we have now.

> Second, I found that all my indexes migrated flawlessly,
> except for the unique primary key indexes!  PostgreSQL and
> MSSQL have a major difference when you use CREATE TABLE ...
> PRIMARY KEY.  Postgres simply creates UNIQUE  and NOT NULL
> constraints.  MSSQL does all that, plus it creates a unique
> index for the column.  Perhaps in future versions of the
> wizard, these indexes would also be migrated?  I saw a bunch
> of indexes on all my migrated tables, and foolishly assumed
> the PK indexes were there as well.

The Wizard does migrate pkeys if it can identify them (which it does
using Microsoft's ADOX library). PostgreSQL itself however does just
create a unique index & not null (which in relational theory is all a
pkey is - unique, not null, and an individual row identifier), the only
difference from any other is (off the top of my head)
pg_index.indisprimary = TRUE for that index. There may also be
additional info in pg_constraint in 7.3. You might try hacking the
system catalogues if you're feeling adventurous. Backup first though!!

Regards, Dave