Migrating SQL Server to PostgreSQL

1. download and install latest postgresql (eg: 10.4)
   download and install latest admin tool (eg: pgadmin 4.3)
2. create a postgres “Server” that matches the actual server name, and a database

5. download the newest 32 bit postgresql driver (eg: psqlodbc_x64.msi)
6. install the driver (image) …

7. run SSIS export wizard (image) …

Where the wizard stops with an error use the following script to change the offending column to varchar(max). The idea at this stage is just to get the data in, in whatever form.

-- convertTablesForPg.sql

-- 1. to find tables with a named column, or

select
so.[name] table_name, sc.[name] column_name, st.[name] data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.[type] = sc.[type])
where so.[type] = 'U'
and sc.[name] = 'Email'

-- 1b. to find named tables

select
so.[name] table_name, sc.[name] column_name, st.[name] data_type,
'ALTER TABLE [dbo].[' + so.[name] + '] ALTER COLUMN [' + sc.[name] + '] VARCHAR(MAX);'
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.[type] = sc.[type])
where so.[type] = 'U'
and so.[name] = 'translations'
ORDER BY 2

-- 2. to create command to change date/time/bit columns to varchar(max)

select
so.[name] table_name, sc.[name] column_name, st.[name] data_type,
'ALTER TABLE [dbo].[' + so.[name] + '] ALTER COLUMN [' + sc.[name] + '] VARCHAR(MAX);'
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.[type] = sc.[type])
where so.[type] = 'U'
and st.[name] IN ('DATE', 'TIME', 'BIT')
ORDER BY 1,2,3

/* missed tables
[dbo].[BuyersBySupplierData]
*/

See me for part 2 😉