Thread: Converting a table from SQL Server
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
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
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. Thanks, Bob McConnell N2SPP
On Sun, Aug 31, 2008 at 10:19 AM, Bob McConnell <rmcconne@lightlink.com> 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. Thankfully, Postgresql SQL generally conforms to the SQL standard. I would suggest working through some simple test examples found online. You'll learn a great deal about SQL by just typing in examples and getting familiar with the tools available. Then, you can peruse the manual to learn more detail and some of the edge cases that you might want to employ. Sean
Bob McConnell <rmcconne@lightlink.com> writes:
> 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?
I believe the main thing you need to know is that the brackets are
a nonstandard spelling for quoted identifiers. That is
[MajorReleaseNumber] converts to "MajorReleaseNumber".
(You might be better off translating to MajorReleaseNumber without the
quotes, which will really mean majorreleasenumber. Depends whether you
want to double-quote every use of the name in your applications.)
The IDENTITY business probably equates to SERIAL, and there are some
other nonstandard things here like the CLUSTERED adjective.
regards, tom lane
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