Re: Migrating from MS SQL server

> Hello,
>
> I'm just new on this list and hope that I don't ask a FAQ (if so please
> lead me to the correct document).
>
> I have to migrate a smaller Database (about 20 tables, fully normalized,
> no table more than 1000 lines) for a web-project from MS SQL server
> to PostgreSQL.
>
> Each table has an unique ID which I plan to port to a SEQUENCE.
> Because there were some deleted rows the current database I'm affraid
> that the PostgreSQL tables will not be filled correctly because
> the IDs are refered to in other tables and are not possible to
> change. I wonder how to assure the referential integrity of the
> tables.
> Are there any mechanisms in PostgreSQl to assure that only those
> numbers could be included into a table which are present as ID in
> a certain row and how to do that if it is possible?
>
> Furthermore I have some stored procedures. Do I have to expect
> many problems when converting to PostgreSQL?
>
> If you answer my questions please keep in mind that I consider
> myself as a beginner in SQL and databases and as quite experienced in
> handling Linux.
>
> Kind regards
>
> Andreas.
>
>

There is a great replication mechanism in MS SQL. You should set up your MS
SQL server as Distributor and Publisher, set up a snapshot replication, add
a pull ODBC Subscriber with DSN of your PostgreSQL server, and start the
Replication Agent (see MS SQL Server documentation for details). All your
indices, constraints etc. that PostgreSQL supports will be ported there.
Data will be bulk copied into PostgreSQL database first, and constraints
will be applied later, so all your data will be just the same it was on MS
SQL Server.

As for unique ID's, there are two types of ones in MS SQL: identity and
GUID's (called uniqueidentifiers in MS SQL). If you use identity (that is
just an autoincrement integer field), you will have no problems with porting
it, but you will have to create manually a PostgreSQL generator for each
table, set its value to SELECT MAX(id) FROM table, and add a default of
SELECT NEXTVAL('generator_name') to the id field of the table. GUID's are
converted into CHAR(36) type, and you will have to write an SQL function
that would generate a GUID and set default value of your GUID field to this
function. I heard that Linux library with the GUID generation function exist
somewhere, but I may mistake.

Triggers and stored procedures are to be completely rewritten, because
languages that MS SQL and PostgreSQL use are very different.

Alex Bolenok.