From:       To:      
Home > Documentation > SQL Server to PostgreSQL

How to Control Results of SQL Server to PostgreSQL Migration

[SQL Server 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

SQL Server exposes table structure as follows:

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

Table structure is converted from SQL Server to PostgreSQL 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 SQL Server data type.


SQL Server to PostgreSQL data migration can be validated by comparison of random data fragments from the source and destination tables. SQL Server 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 SQL Server 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

SQL Server 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

SQL Server exposes foreign keys information as follows:

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

    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
    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';


The only way to check that all views have been converted from SQL Server to PostgreSQL properly is analyzing code of each view in the source and target databases 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.

SQL Server 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.