Anyone who plans to convert SQL Server to PostgreSQL must understand what exactly should be validated in PostgreSQL database after completing the migration process. Here’s a complete list of database objects which need to be verified:

• The Table Structures
• The Data
• The Primary Keys & Indexes
• The Foreign keys
• The Views

Table Structures

MS SQL Server exposes the table structure as seen below:

• In the T-SQL client, run this statement EXEC sp_columns @table_name=(table name)
• In the Management Studio, simply expand database in the left pane, then expand ‘Tables’ node, then right-click on table name and then select ‘Design’ item

PostgreSQL console client will explore the table definition by running this statement \d table_name

Microsoft SQL table structure is then converted systematically when each column has equal type, size and default value with the corresponding PostgreSQL table.

Data

The converted data can now be easily validated by using visual comparison of certain fragment(s) from MS SQL and the Postgres tables. MS SQL allows exploring data fragment as given below:

• In T-SQL client run this statement SELECT TOP number_of_records * FROM table_name
• After that, go to Microsoft Management Studio and right-click on the table in the left pane and there select ‘Select Top 1000 Rows’ item

PostgreSQL will accept this kind of SELECT-query to extract the fragment of data:

SELECT * FROM table_name LIMIT number_of_records

It is also very important to check that MS SQL and PostgreSQL tables have the exact same number of records. Both the DBMS allows to get number of table records given below:

SELECT COUNT(*) FROM table_name

Primary Keys and Indexes

MS SQL allows to list indexes as follows:

• In T-SQL console client run this SQL statement

SELECT o.name AS Table_Name,

       i.name AS Index_Name,

       i.type_desc AS Index_Type

FROM sys.indexes i

INNER JOIN sys.objects o ON i.object_id = o.object_id

WHERE i.name IS NOT NULL AND o.type = ‘U’

ORDER BY o.name, i.type

• In the Management Studio, open the ‘Design’ view of the table (refer details given in ‘Table Structures’ section of this guide) and then click ‘Manage Indexes and Keys’ button right on the toolbar (marked red on the screenshot above)

PostgreSQL displays info about indexes that’s at the bottom of table definition generated by the command: \d table_name

Foreign Keys

MS SQL exposes details of foreign keys information as given below:-

• In T-SQL console client run SQL statement

SELECT obj.name AS fkey_name,

       systab.name AS source_table,

       syscol.name AS source_column,

       reftab.name AS referenced_table,

       refcol.name AS referenced_column

FROM sys.foreign_key_columns fkc

INNER JOIN sys.objects obj

       ON obj.object_id = fkc.constraint_object_id

INNER JOIN sys.tables systab

       ON systab.object_id = fkc.parent_object_id

INNER JOIN sys.columns syscol

       ON col1.column_id = parent_column_id AND syscol.object_id = systab.object_id

INNER JOIN sys.tables reftab

       ON reftab.object_id = fkc.referenced_object_id

INNER JOIN sys.columns refcol

      ON refcol.column_id = referenced_column_id AND refcol.object_id = reftab.object_id

• Go to Management Studio, open ‘Design’ view of the table and then click ‘Relationships’ button on the toolbar (at the left of the conrtol described in previous section, refer screenshot given above)

PostgreSQL can extract info about foreign keys from the 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 verify that all views have been converted in a proper manner is via the comparison code of each view in Microsoft SQL and PostgreSQL taking into account the differences between SQL dialects of the two DBMS. This is how you will get the list of all views in source and in the destination databases.

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

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

Tweet