Re: Converting a table from SQL Server

Bob McConnell wrote:
> Sean Davis wrote:
>> On Sun, Aug 31, 2008 at 9:03 AM, Bob McConnell
>> <rmcconne@lightlink.com> wrote:
>>> I am just beginning to learn a number of new tools simultaneously, so
>>> please
>>> bear with me. I have installed Apache 2.2, PHP 5 and PostgreSQL 8.2.1
>>> on a
>>> couple of servers to play with. I also have pgAdmin III 1.8.4 running
>>> on one
>>> workstation which is able to connect with each server. I am not yet
>>> fully
>>> happy with the results, but they are close enough now for me to start
>>> trying
>>> a few experiments.
>>>
>>> I found the text below while searching for something on Google. Based
>>> on the
>>> site it was posted to, I believe it is for SQL Server. I would like to
>>> convert it into Postgres and make it a standard component of every
>>> database
>>> I build. (I added the PatchNumber field.)
>>>
>>> But I have only found articles on how to convert from MySQL to
>>> Postgres and
>>> a few on how to convert from SQL Server to MySQL. So how do I
>>> translate this
>>> without leaving the bad taste of MySQL in my mouth? Or is there a
>>> similar
>>> recommended practice for Postgres?
>>
>> Do you mean that you want an auto-translator for SQL Server to
>> Postgres?  Or do you mean that you just need help with Postgresql
>> syntax?  If it is the latter, the docs for postgresql are quite good:
>>
>> http://www.postgresql.org/docs/8.2/static/
>>
>> Sean
>>
>
> In this case, I just want to manually translate these lines from
> Microsoft SQL to Postgres SQL so I can append them to every database and
> script I build. Since I don't know either language yet, and have no
> desire to learn the Microsoft (nor MySQL) variation, I don't know the
> best way to proceed. What makes it even more confusing is that I know
> just enough Sybase ASA SQL to be dangerous. That's the one I have had to
> deal with at work for the past ten years.
>
> I know, the best thing about standards is that there are so many to
> choose from.


Ok, just to close the loop on this old question, I finally got around to
finalizing this translation. Below is the table I am adding to each new
database I create.

-----8<--------------------------------------------
CREATE TABLE schema_changes(
    id serial PRIMARY KEY,
    majorrelease varchar(2) NOT NULL,
    minorrelease varchar(2) NOT NULL,
    pointrelease varchar(4) NOT NULL,
    patch varchar(4) NOT NULL,
    scriptname varchar(50) NOT NULL,
    dateapplied timestamp without time zone NOT NULL
);

INSERT INTO schema_changes (
        majorrelease
        ,minorrelease
        ,pointrelease
        ,patch
        ,scriptname
        ,dateapplied
        )
VALUES (
        '01'
        ,'00'
        ,'0000'
        ,'0000'
        ,'createdb.pgsql'
        ,now()
        );
-----8<--------------------------------------------

Bob McConnell
N2SPP