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;
