Skip site navigation (1) Skip section navigation (2)

Re: Converting a table from SQL Server

From: Bob McConnell <rmcconne(at)lightlink(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Converting a table from SQL Server
Date: 2010-04-17 19:21:54
Message-ID: 4BCA0A52.2030203@lightlink.com (view raw or flat)
Thread:
Lists: pgsql-novice
Bob McConnell wrote:
> Sean Davis wrote:
>> On Sun, Aug 31, 2008 at 9:03 AM, Bob McConnell 
>> <rmcconne(at)lightlink(dot)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

In response to

pgsql-novice by date

Next:From: Michael WoodDate: 2010-04-17 19:39:42
Subject: Re: Creating a db on my newly installed postresql on OS X
Previous:From: Tom LaneDate: 2010-04-16 20:08:13
Subject: Re: Creating a db on my newly installed postresql on OS X

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group