The truth about SQL Server to PostgreSQL migration

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