Re: SQL Server 2000 to PostgreSQL 8.0.3

Only thing you may run into moving data is field types.
I had to convert my bit fields to binary and my datestammp to timestamp.
I would just be sure the field types are supported or you may have to do a
search replace on the text file created by aquastudio.

Although when I used aqua I exported using aqua and used pgadmin to do the
inserts so I had to also change their go to a ; (which was actually an
option on the export page I did not see at first).

Best of luck with the conversion.

Also here is my cheat sheet of stuff I looked at often while converting it
involves some sql differences.
The isnull is now coalesce, and date diff is a just math, so I had to
reverse the order of my date diffs and convert to date - date


date_part('epoch',date-date) returns in secs so /60

  to_char( i.opendate,'yyyy') as year,
  to_char( i.opendate,'q') as quarter,
  to_char( i.opendate,'MM') as month,
  to_char( i.opendate,'D') as weekday,
  to_char( i.opendate,'WW') as week,
  to_char( i.opendate,'HH24:MI') as time


coalesce(to_char(tblresponsesection.feedbackcompleteddate,'Mon DD YYYY'),
'not completed') as completed

Left 105 chars                      substring(tblquestions.question,1,105)
as question,

Right 5 chars                      substring(c.casenum::text, 1, 11),
length("substring"(c.casenum::text, 1, 11))-5, 5)

                      coalesce(c.apprehdate, c.opendate) as apprehdate,

                      to_char( coalesce(c.apprehdate,c.opendate),'yyyy') as
year,

                      to_char( coalesce(c.apprehdate, c.opendate),'q') as
quarter,

                      to_char( coalesce(c.apprehdate, c.opendate),'MM') as
month,

                      to_char( coalesce(c.apprehdate, c.opendate),'D') as
weekday,

                      to_char( coalesce(c.apprehdate, c.opendate),'WW') as
week,

                      to_char( coalesce(c.apprehdate, c.opendate),'HH24:MI')
as time,

~* is a  = that is not case sensitive same as = is mssql
ilike is a case insensitive like

current_timestamp

SET ENABLE_SEQSCAN=FALSE;
You will want to get used to doing
explain analyze in front of your sql to see what you may need to modify or
key. I would subscribe to the performance list sql list to see how folks do
stuff. I had to de-normalize some of my larger tables sets and now my
performance is as good as my SQL server was.
Ex: explain analyze select * from tblcase where clientnum = 'WAZ' select *
from tblcase where clientnum = 'WAZ'
Will display the joins etc involved in getting the data.


Joel Fradkin

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of josh@segrestfarms.com
Sent: Tuesday, August 09, 2005 8:37 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] SQL Server 2000 to PostgreSQL 8.0.3

Greetings.

I am having difficulty moving a SQL Server 2000 database over to
PostgreSQL 8.0.3.

In SQL Server, I am performing a backup of the database I want to move.
I save that backup on my Desktop.

I am then opening up PGAdminIII and attempting to perform a Restore of
the database saved on my Desktop.  The first step I take is to create a
new database, MyDB.  Next, I highlight my new database and then select
Restore from the Tools menu.  The Restore Database window pops up and I
Browse to the database backup I've saved on my Desktop.  After selecting
the backup, the 'OK' button stays grayed and I am unable to proceed.

Any ideas as to how I can work around this?  Thanks in advance.


Josh in Tampa

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings