SQL code is used in stored procedures, functions and triggers to encapsulate the logic of data processing. Therefore, one of the most important and challenging step of the database migration is to convert SQL from SQL Server to PostgreSQL.
One of the best practices for translating stored procedures, functions, triggers and views from SQL Server or Azure SQL to PostgreSQL is to ensure that you understand both systems and resulting code is acting the same way as the original, instead of simply converting the syntax patterns.
Unlike the data migration, there is no compete solution to convert SQLfrom SQL Server or Azure SQL to PostgreSQL since this is a quite complicated task. This article explores some tips and tricks to help database engineers or other responsible staff migrate source code between the two DBMS.
There are many differences in syntax and semantics of SQL in these two DBMS. One of the key difference that must be constantly under control is that PostgreSQL is a strongly typed language. While SQL Server can dynamically determine the type of returned data and cast it properly, PostgreSQL requires explicit types casting. One of solutions for this issue while convert SQL is to use PostgreSQL pseudo-type“any element” which purpose is to indicate that function accepts any data type.
It is also important to note that there is a difference in how variables are declared in SQL Server and PostgreSQLcode. In SQL Server, variables can be declared anywhere, while in PostgreSQL, local variables must be declared at the beginning of the stored function or procedure. This syntax difference requires a change in habit.
Additionally, if your application uses proprietary features of SQL Server, you will need to rewrite those parts of the application that use SQL Server. It is important to have a good understanding of both DBMS capabilities and features as well as differences in syntax and functionality between the two systems before beginning to convert SQL from SQL Server to PostgreSQL.
When converting SQL Server queries that contain ‘TOP N’, it must be converted into PostgreSQL syntax depending on N-value as follows:
- If N=’(100) PERCENT’, then it should be omitted in PostgreSQL query
- If N=’XXX PERCENT’, then it should be replaced by ‘LIMIT YYY’ where YYY=number of rows * XXX / 100
- Otherwise it should be simply replaced by ‘LIMIT N’
The table below illustrates how to convert SQL of the most common built-in functions from SQL Server to PostgreSQL:
| SQL Server | PostgreSQL |
| CHARINDEX | POSITION |
| DATEADD($interval, $n_units, $date) | ($date + $n_units * $interval)::date |
| DATEDIFF($interval, $date1, $date2) | date_part(‘day’, $date2 – $date1) |
| DATEPART | DATE_PART |
| GETDATE | NOW |
| ISNULL | COALESCE |
| REPLICATE | REPEAT |
Sounds complicated? Then it is reasonable trying special tools to convert SQL that can partially automate migration of stored procedures, functions, triggers and views from SQL Server or Azure SQL to PostgreSQL. One of such tools is SQL Server to PostgreSQL Code Converter developed by Intelligent Converters.
The product has the following capabilities:
- Convert SQL of stored procedures, functions, triggers and views
- SQL Server built-in functions are converted into PostgreSQL equivalents
- Predefined SQL Server types are mapped into PostgreSQL (including spatial and other complicated types)
- Reserved words and identifiers are handled into the most intelligent way
- Source code may be extracted either from database directly or form T-SQL script file
Although SQL Server to PostgreSQL Code Converter automates migration of the basic syntax patterns and constructions, some manual post-processing of the output code may be required, especially for large and complicated fragments of the source code.
Visit official site of Intelligent Converters to learn more about how to convert SQL from SQL Server to PostgreSQL.