Home > Documentation > MS SQL to PostgreSQL

How to Control Results of MS SQL to PostgreSQL Migration

[MS SQL to PostgreSQL Converter]  [About Migration]  [Types Mapping]

It is reasonable to begin with understanding what exactly must be validated in PostgreSQL database after migration is complete. Here is list of database objects that have to be verified:

Table Structures

Microsoft SQL Server exposes table structure as follows:

PostgreSQL console client explores table definition by running the statement \d table_name

MS SQL table structure is converted properly when each column has equal type, size and default value in the corresponding PostgreSQL table. Here is the table of appropriate conversions for each Microsoft SQL data type.

Data

Converted data can be validated by visual comparison of certain fragment(s) from MS SQL and Postgres tables. Microsoft SQL allows to explore data fragment as follows:

PostgreSQL accepts this kind of SELECT-query to extract fragment of data:

SELECT * FROM table_name LIMIT number_of_records

Also, it is important to check that Microsoft SQL and PostgreSQL tables have the same number of records. Both DBMS allows to get number of table records as follows:

SELECT COUNT(*) FROM table_name

Primary Keys and Indexes

Microsoft SQL allows to list indexes as follows:

PostgreSQL displays information about indexes at the bottom of table definition generated by the command: \d table_name

Foreign Keys

MS SQL exposes foreign keys information as follows:

PostgreSQL can extract information about foreign keys from service table "information_schema":

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='table_name';

Views

The only way to check that all views have been converted properly is comparison code of each view in MS SQL and PostgreSQL taking into account differences between SQL dialects of these two DBMS. This is how to get list of all views in source and destination databases.

Microsoft SQL exposes list of all views in the database using the query: SELECT * FROM sys.views

PostgreSQL can do the same via the query: SELECT table_name FROM INFORMATION_SCHEMA.views;

Basic differences between SQL Server and PostgreSQL queries are listed in this article.