Connect with us

Resources

Verifying SQL Server to Postgres Migration

kokou adzo

Published

on

MacBook Pro with images of computer language codes

The most important phase of database migration is validating results at the end of the process. Whether you migrate manually or via special database migration tool, it is extremely important to understand what and how to check after the procedure is completed. This whitepaper explains how to verify the primary database entries:

  • Schemas
  • Data
  • Indexes
  • Views

Schemas

Picture 1 16

SQL Server allows to explore schemas in two ways.

  • In T-SQL console client use the query EXEC sp_columns @table_name=’table_name’
  • In Management Studio click on database name in the left pane, expand ‘Tables' item, right-click on the appropriate name and select ‘Design' item

PostgreSQL displays table structure through the query: \d table_name

Schema is considered as properly converted from SQL Server to Postgres when each column has the same type, size and default value in the resulting table. This is the list of safe type mapping:

SQL Server PostgreSQL
BIGINT BIGINT
BINARY(n) BYTEA
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
DATE DATE
DATETIME TIMESTAMP(3)
DATETIME2(p) TIMESTAMP(p)
DATETIMEOFFSET(p) TIMESTAMP(p) WITH TIME ZONE
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION DOUBLE PRECISION
FLOAT(p) DOUBLE PRECISION
INT, INTEGER INT, INTEGER
MONEY MONEY
NCHAR(n) CHAR(n)
NTEXT TEXT
NUMERIC(p,s) NUMERIC(p,s)
NVARCHAR(n) VARCHAR(n)
NVARCHAR(max) TEXT
REAL REAL
ROWVERSION ROWVERSION
SMALLDATETIME TIMESTAMP(0)
SMALLINT SMALLINT
TEXT TEXT
TIME(p) TIME(p)
TIMESTAMP BYTEA
TINYINT SMALLINT
UNIQUEIDENTIFIER CHAR(16)
VARBINARY(n), VARBINARY(max) BYTEA
VARCHAR(n) VARCHAR(n)
VARCHAR(max) TEXT
XML XML

Data

To verify the data is migrated from SQL Server to Postgres properly, it is required to do visual comparison of random fragment(s) in the source and target tables. SQL Server provides two options to extract data fragment:

  • In T-SQL console client use the query SELECT TOP number_of_records * FROM table_name

or

  • Click on the appropriate table name in the left pane of Microsoft Management Studio and select ‘Select Top 1000 Rows' menu item

Postgres extracts fragment of data through the query:

SELECT * FROM table_name LIMIT number_of_records

It is also reasonable to check that source and destination tables have the same number of records. Both SQL Server and PostgreSQL display number of rows as follows:

SELECT COUNT(*) FROM table_name

 

Indexes

Index is correctly converted from SQL Server to Postgres when it is built on the same columns going in the same order in both source and target tables. Number of indexes and uniqueness also must be the same.

SQL Server provides two options to explore indexes:

Picture 1 17

  • In T-SQL console client run the query

SELECT o.name AS Tbl_Name,

i.name AS Idx_Name,

i.type_desc AS Idx_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 Management Studio, open ‘Design' view of the table (see ‘Schemas' section above) and click ‘Manage Indexes and Keys' icon on the toolbar (it is marked red on the screenshot)

PostgreSQL describes indexes at the bottom of table definition generated by ‘\d table_name’ statement.

Views

In order to check that the view is converted from SQL Server to Postgres properly, it is required to compare SQL code of this view in the source and target databases with respect to differences between SQL dialects of these two database management systems.

SQL Server allows explore views list using the query:

select TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS where TABLE_NAME not in (‘sysalternates','sysconstraints', ‘syssegments')

PostgreSQL uses this query for the same purpose:

select viewname, definition from pg_catalog.pg_views where schemaname NOT IN (‘pg_catalog', ‘information_schema')

Here are the most common steps to make SQL Server view or query compatible with PostgreSQL format:

  • All square brackets acting as delimiters for object names must be replaces by double quotes
  • Default schema ‘dbo’ must be replaced by ‘public’ in all references to database objects
  • Query modifier ‘TOP (100) PERCENT’ means to return all rows of the resulting rowset. It must be removed in PostgreSQL equivalent. Other variations of ‘SELECT TOP N’ must be converted into SELECT ... LIMIT N
  • SQL Server offers ‘FOR XML PATH’ pattern used to merge multiple values into a comma separate string. For example:

SELECT DISTINCT r.category, STUFF(

(SELECT distinct ‘,'+ Cast(a.itemcode as varchar) FROM tbl_price a

WHERE r.category = a.category FOR XML PATH(”), TYPE).value(‘.','VARCHAR(max)'), 1, 1, ”

) FROM tbl_price r

It must be migrated from SQL Server to Postgres using STRING_AGG function:

SELECT category, string_agg(DISTINCT itemcode::varchar,',') code

FROM tbl_price GROUP BY price

  • All system or embedded functions involved in views must be migrated from SQL Server to Postgres according to the following table:

SQL Server PostgreSQL
CHARINDEX POSITION
DATEADD($interval, $n_units, $date) $date + $n_units * interval ‘{name}’
DATEPART DATE_PART
GETDATE() NOW()
IIF(condition, val1, val2) CASE WHEN condition THEN val1 ELSE val2 END;
ISNULL COALESCE
REPLICATE REPEAT

Find more details on database migration from SQL Server to Postgres at the official site of Intelligent Converters, one of the leading software companies in the field of database migration and synchronization.

Kokou Adzo is the editor and author of Stri.us. He is passionate about business and tech, and brings you the latest Startup news and information. He graduated from university of Siena (Italy) and Rennes (France) in Communications and Political Science with a Master's Degree. He manages the editorial operations at Stri.us.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Top of the month