The truth about SQL Server to PostgreSQL migration

26 January,2022 by Rambler

Depending on the complexity and feature usage within SQL Server - a migration or conversion to PostgreSQL can be a challenge

 

Geographic data 

 PostgreSQL does not have geographic native data type.

Case sensitivity 

  PostgreSQL is case sensitive for evaluating strings

Index differences

   - PostgreSQL - B-tree , hash, Generalized Search Tree , Space Partitioned , Generalized Inverted Index, Block Range Index

    - SQL Server - Clustered Index , Non Clustered Index

Replication differences

   - PostgreSQL - Primary > Secondary Replication

   -SQL Server - Primary > Secondary : transactional , merge , snapshot 

High Availability 

    -PostgreSQL - shared disk failover, write ahead log shipping , disk partitioning , 

    - SQL Server - replication , log shipping , failover clusters, AlwaysOn

Stored Procedures

     - PostgreSQL - stored procedures as UDF with RETURN VOID clause

    - SQL Server - columns are defined

Query language

     - postgreSQL - PL/pgSQL

    - SQL Server - T-SQL

Regular Expressions

     -PostgreSQL - LIKE, SIMILAR TO , POSIX

     -SQL Server - no native support for regular expression , but use of LIKE , SUBSTRING,PATINDEX

Table Statements

    -PostgreSQL - TRUNCATE   , Support for Inheritance 

   - SQL Server - TRUNCATE TABLE,  No support for Inheritance

Column Differences

    -PostgreSQL - Generated as Identity 

   - SQL Server  - Identity Column 

Data Types  - There are multiple data type incompatibilities 

   

     

Integers

    - PostgreSQL - SMALLINT , INT, BIGINT

    - SQL Server - BIGINT, INT,SMALLINT, TINYINT

Boolean 

      - PostgreSQL - Boolean Data type

     - SQL Server - BIT Data type

DatePart

       -PostgreSQL - date_part

       - SQL Server - DATEPART

DateAdd 

         -PostgreSQL - SELECT CURRENT_DATE + INTERVAL ‘4 day’;

       - SQL Server - SELECT DATEADD(day, 4, GETDATE());

ISNULL

      -PostgreSQL - COALESCE

       - SQL Server - ISNULL

GETDATE

        -PostgreSQL  - NOW()

        -SQL Server - GETDATE()

CHARINDEX

        -PostgreSQL - POSITION

        - SQL Server - CHARINDEX

String Concatenation  

      - PostgreSQL - SELECT MyFirst || MLastName FROM Myemployees;

       - SQL Server - SELECT MyFirst + MLastName FROM Myemployees;

Security 

       - PostgreSQL - SSL , Kerberos authentication

      - SQL Server  - TLS , encryption, Kerberos authentication

 

 

 

 

 

    

 

 

 

 

 


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on The truth about SQL Server to PostgreSQL migration


dba-ninja.com