Re: migrating from MSSQL

Hi

I have migrated a very large database from MSSQL to PG. This was a
database of +/- 400 tables. You should note the following.

- MSSQL datatypes are not always compatible with PG datatypes. If you do
this carefully PG will save you a lot of time.
- The "timestamp" datatype of MSSQL is not the same thing in PG. The
"timestamp" is from MSSQL 7 and 2000 days
- Check if all the built-in functions that you are using in MSSQL also
are available in PG (most are by the way)
- Auto numbering is MSSQL is different from PG. In PG you have to do
with sequences (serial datatype)

- About the stored procedures:

The first thing you will notice in migrating stored procedures is that
in PG you will have to define what the return type will be of your
stored procedure.
This might be a little frustrating because if you have a stored
procedure that returns a set of columns which belong to various tables,
you then have to create a custom type that exactly matches your stored
procedures number (and type) of columns or use the "record" datatype as
return type. (I can tell you that this is not very handy)

Please note that TSQL is a very different language. In my case 70% of
all the stored procedures had to be customized one way or another.

Entity names in PostgreSQL are down folded by default: CREATE TABEL
MyTable results the table name to be mytable (lower case).
This causes a lot of problems if your application (especially C/C++ apps
) expects the column names to be case preserved.
In order to achieve case preserving in PG you must do: CREATE TABLE
"MyTable" (note the quotes around the table name)

- I have had no problems migrating constraints and indexes.
- Triggers are also different but very easy to migrate.
- I had to write a little application for migrating data. because I had
to check and modify data before I inserted them into PG. Perhaps you can
export the data to CSV and then import it in PG using the COPY command.

I hope this helps,

Regards,
Gevik.

Eugene . wrote:
> Hi all,
>
> I've been tasked with evaluating the feasibility of migrating our
> in-house application from MSSQL to PostgreSQL. It is fairly old and
> has lots of stored procedures, which is why we need to think carefully
> before making the switch. Does anyone else have a similar experience?
> What are some of the things to watch out for?
>
> Secondly, which commercial support vendor would you recommend? I found
> EnterpriseDB and CommandPrompt, but I don't know anything about them.
> Any other candidates?
>
> thanks,
>
> Eugene
>