Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    England
    Posts
    15

    Unanswered: SQL Server 2k migration to PostgreSQL

    Hi all

    I hope you can help me in some way.

    Basically for the last 5 months i've developed a SQL 2k db for my company.

    All of a sudden they quite fancy using Linux on the server, therefore i would be required to use PostgreSQL instead of SQL Server 2k.

    I could do with some advice about how PostgreSQL and SQL Server compare? and how much work would be involved in migrating from one to the other.

    Points i've found myself and may be an issue are:

    I've used some of SQL Servers XML capabilites and as far as i can tell PostgreSQL doesn't support XML.

    I've made extensive use of functions that return table variables etc... is there a similar idea i can use in PostgreSQL?

    Extensive use of Stored Procedures and T-SQL etc...

    DTS packages. Any equivalent or similar idea?

    (ps i've also posted this in the PostgreSQL but since no one replyed i was hoping the sql server boffs could help).

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    PostgreSQL doesn't directly support XML, but that support can be added "after the fact" with a bit of Perl or PHP. This is clumsy compared to MS-SQL, but it works.

    I can't think of any way to approximate a sql-2000 table valued function in PostgeSQL. That doesn't mean there is no way to do it, just that I can't think of one.

    PostgreSQL does support stored procedures, at least in a fashion. As the support varies a great deal from one version to the next, I'll let you read up on what your version supports. As a side note, since the changes are significant from version to version, that kind of implies that you want to implement the newest possible version when you "go live", then delay upgrades for as long as you can (until the new features offered outweigh the work needed to get to the point that you can use those features).

    PostgreSQL supports SQL. It doesn't really have what I consider a "local programming language" like T-SQL or PL/SQL, even though it has come a long way from where it was. This isn't as big a problem as it might seem, since you can easily wrap a bit of Perl, PHP, or Python around the PostgreSQL and have a full-blooded scripting/programming language that will beat the pants off of the best programming language(s) offered by DBMS engines.

    Almost any ETL package offers features similar to DTS. They aren't always as convenient and never as well integrated into the SQL engine, but they will do the job. This is part of the price you pay for vendor independance, and while conversion projects can kill you, that price is often very acceptable for new development.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Location
    England
    Posts
    15

    Thumbs up

    Pat thanks for the advice, much appreciated.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    De nada! I'll email you a bill for a virtual ice cream cone!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •