Re: MSSQL to PgSQL

Mike Hammonds wrote:
>
> ** Reply Requested When Convenient **
>
> Can PgSQL use SQL scripts generated from MSSQL? see the attached script
>
> Mike Hammonds, CNE5, MCP-NT
> Fellon-McCord & Associates, Inc.
> Office: 502.214-6324
> Fax: 502.426.8800
> mhammonds(at)knowledgeinenergy(dot)com
> Show Me the Code!!
>
> ------------------------------------------------------------------------
> Name: energy2.sql
> energy2.sql Type: unspecified type (application/octet-stream)
> Encoding: base64

Well, no matter what 'file' says that ain't an MP3 :-)

After I used 'tr' to get the nulls out I can see that it won't work in
PostgreSQL because:

IF (SELECT COUNT(*) FROM deleted, tblDealLog
WHERE (deleted.ContactNo = tblDealLog.ContactNo)) > 0
BEGIN
RAISERROR(778584, 16, 1)
ROLLBACK TRANSACTION
END

GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

1) PostgreSQL doesn't use 'GO' as an end of command indicator, it uses a
semi-colon - that should be a fairly straightforward replacement.

2) PostgreSQL doesn't have a syntax like that for any RAISERROR
function. But you could implement a function called RAISERROR which
took three parameters and did something with them.

3) PostgreSQL will not allow multiple SET ... commands without normal
statement separators between them. I don't think that those ones would
be valid in PostgreSQL anyway.

CREATE TRIGGER tblVendorContacts_UTrig ON tblVendorContacts FOR UPDATE
AS
/*
* PREVENT UPDATES IF NO MATCHING KEY IN 'tblVendorAddress'
*/
IF UPDATE(VendorOwnerNo)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblVendorAddress, inserted WHERE
(tblVendorAddress.VendorOwnerNo = inserted.VendorOwnerNo))
BEGIN
RAISERROR(778573, 16, 1)
ROLLBACK TRANSACTION
END
END

4) PostgreSQL syntax for triggers is substantially different to this,
viz:
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func ( arguments )
which is not too surprising as there is no CREATE TRIGGER in the SQL-92
standard (according to PostgreSQL docs.

5) PostgreSQL does not have "BEGIN" or "IF" constructs in SQL (well,
there is "CASE") but that's OK since they are in PL/PGSQL, which is what
most medium complexity trigger procedures are going to end up written in
anyway.

Everything else in the file seems to be pretty plain SQL, which should
pretty much work, except that making these conversions might not be
sensible. The SQL in that file appears to implement things like
referential integrity constraints, and in PostgreSQL may well be better
implemented as such constraints, specified within the CREATE TABLE
statement.

There are some tools for converting databases from MS SQL to PostgreSQL
as well. I haven't used them, I'm afraid, but I did look into it and
some of them seem particularly good.

I recently had to convert an MS SQL database to PostgreSQL and I chose
to do it by dumping the SQL to build the database and then hand-editing
it into a PostgreSQL database, editing out some existing data design
flaws in the process. Once I had the data structures set up to my
liking I actually wrote a conversion process that exported the MS SQL
data into CSV and then used Perl and SQL to load it and massage it into
the PostgreSQL database.

While this may all sound pretty laborious, I was also converting the
application from ASP to PHP at the same time, so there was definitely
value in fixing design flaws at the same time. If you are not dependant
on having exactly the same set of tables and fields I would recommend
the approach.

Regards,
Andrew McMillan

PS. Good luck!
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267