Migrating current SQL Server database to PostgreSQL

• Postgres collation : SQL_ASCII (not UTF-8! )
• MSSQL collation irrelevant

1. Using DBConvert (1st run): copy ONLY STRUCTURE, no data
2. Dont copy _MigrationHistory
3. After structure is copied, login to Postgres and change ALL columns in the database except *_pkey and *_key from varchar to text, using the first script below
4. Re-run DBConvert (2nd run) and copy data. Ignore errors about skipping created tables - thats OK
5. Check tables 'PickList' and 'PickListAnswers' in converted database. If tables are empty relaunch convertion two additional times: first one with only 'PickList' from source databse, second one only 'PickListAnswers'
6. Execute rest of the scripts below

YOU MUST BE LOGGED INTO CURRENT DATABASE ! ALL RESULTS OF THESE SCRIPTS MUST BE EXECUTED TO CORRECT WORK OF CRM !

Don run this script on the root level

The script will generate ALTER commands, paste them into the editor and bulk-run. Skip the rows containing _pkey or _key

   SELECT string_agg
   (format
      ('ALTER TABLE %s ALTER COLUMN %I TYPE text' , a.attrelid::regclass, a.attname),
      E';\n')
   FROM   pg_attribute a
   JOIN   pg_class     c ON c.oid = a.attrelid
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE    a.atttypid = 'varchar'::regtype
   AND    NOT a.attisdropped                      -- no dropped columns
   AND    a.attnum > 0                            -- no system columns (redundant check)
   AND    c.relname NOT LIKE '%_pkey%'
   AND    c.relname NOT LIKE '%_key%' -- not tested should avoid rows with _pkey or _key 
   AND    format_type(a.atttypid, a.atttypmod) LIKE ('character varying(%');

The script will enable module for generating uuid in database

   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

The script will generate ALTER commands setting default value uuid for PK with uuid data type, paste them into the editor and bulk-run.

   SELECT string_agg
   (format
      ('ALTER TABLE %s ALTER COLUMN %I SET DEFAULT uuid_generate_v4()' , a.attrelid::regclass, a.attname),
      E';\n')
   FROM   pg_attribute a
   JOIN   pg_class     c ON c.oid = a.attrelid
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE    a.atttypid = 'uuid'::regtype
   AND    NOT a.attisdropped                      -- no dropped columns
   AND    a.attnum > 0                            -- no system columns (redundant check)
   AND    c.relname NOT LIKE '%_pkey%'            -- skip rows with 'pkey' 
   AND    a.attname = 'Id';                       -- select only PK

The script will generate ALTER commands setting default value as current day for all columns named 'HistoryDate' or 'CreatedDate', paste them into the editor and bulk-run.

   SELECT string_agg
   (format
      ('ALTER TABLE %s ALTER COLUMN %I SET DEFAULT CURRENT_TIMESTAMP' , a.attrelid::regclass, a.attname),
      E';\n')
   FROM   pg_attribute a
   JOIN   pg_class     c ON c.oid = a.attrelid
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE  NOT a.attisdropped                      -- no dropped columns
   AND    a.attnum > 0                            -- no system columns (redundant check)
   AND    c.relname NOT LIKE '%_pkey%'            -- skip rows with 'pkey'
   AND a.attname = 'CreatedDate'                  -- select cols named 'CreatedDate'
   OR a.attname = 'HistoryDate';                  -- select cols named 'HistoryDate'
   OR a.attname = 'UploadedDate';                  -- select cols named 'HistoryDate'