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:Jack Vamvas