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

Converting a table from SQL Server

From: Bob McConnell <rmcconne(at)lightlink(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Converting a table from SQL Server
Date: 2008-08-31 13:03:24
Message-ID: 48BA969C.1020905@lightlink.com (view raw or flat)
Thread:
Lists: pgsql-novice
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?

Thank you,

Bob McConnell
N2SPP

-------------------------------------
At some point in the future, the schema will have to change. Before you
baseline the database you need to add a table to record these schema
changes. The following table is the kind of table I'd use to track
every change to a database.

CREATE TABLE [dbo].[SchemaChanges](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MajorReleaseNumber] [varchar](2) NOT NULL,
    [MinorReleaseNumber] [varchar](2) NOT NULL,
    [PointReleaseNumber] [varchar](4) NOT NULL,
    [PatchNumber] [varchar](4) NOT NULL,
    [ScriptName] [varchar](50) NOT NULL,
    [DateApplied] [datetime] NOT NULL,

     CONSTRAINT [PK_SchemaChangeLog]
         PRIMARY KEY CLUSTERED ([SchemaChangeID] ASC)
)

The first baseline schema script should, as the last step, officially 
install version 1.0 of the database:

INSERT INTO [SchemaChangeLog]
        ([MajorReleaseNumber]
        ,[MinorReleaseNumber]
        ,[PointReleaseNumber]
        ,[PatchNumber]
        ,[ScriptName]
        ,[DateApplied])
VALUES
        ('01'
        ,'00'
        ,'0000'
        ,'0000'
        ,'initial install'
        ,GETDATE())

posted on Thursday, January 31, 2008 11:46 PM by scott

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2008-08-31 14:01:32
Subject: Re: Converting a table from SQL Server
Previous:From: Sean DavisDate: 2008-08-31 09:39:00
Subject: Re: Date Of Entry and Date Of Change

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