I had a chance of working with PostgreSQL and SQL Server at the same time to pull bulk data from PostgreSQL to SQL Server using Linked Server and OPENQUERY function of SQL Server for report writing in SSRS. Though I don't have to work much with PostgreSQL, I thought of comparing these two database server to clear my basic understanding of how PostgreSQL differs from SQL Server.
- Operating System
SQL Server only runs on Windows Desktop/Server whereas PostgreSQL runs on Windows Desktop/Server, Linux, Unix, Mac
SQL Server is commercial closed source and have to pay to use it whereas PostgreSQL is BSD open source database management system that allows you to do anything for making your own commercial product.
SQL Server supports automatically updatable and incexable views whereas for PostgreSQL, you have write rules against views in order to update views.
SQL Server does not have Limit..offset but it supports TOP and ANSI compliant ROW_Number () Over (Order by column_name) whereas PostgreSQL does not support TOP but support Limit..offset and also supports the ANSI compliant Row_Number() over (order by column_name)
SQL Server use plus operator for concatenation. Example: Select HouseNo + ', ' + Line1 + ', ' + County as HomeAddress whereas in PostgreSQL query use pipe sign and previous example becomes Select HouseNo || ', ' || Line1 || ', ' || County as HomeAddress
- Like Statement
SQL Server by default LIKE statements are not case sensitive whereas in PostgreSQL LIKE statements are case sensitive. To make case insensitive we can use ILIKE but in some cases we may get some issue because its not ANSI compliant and ODBC does not exp[ose it. Another way to make case insensitive is by simply making both column data and variable (value to compare) to lower case and compare it. You have to use LOWER function.
In the latest version of PostgresSQL, citext module is integrated which helps to provide case-insensitive character sting type.
According to postgresql.org, "The citext data type allows you to eliminate callse to lower in SQL queries, and allows a primary key to be case-insensitive. citext is locale-aware, just like text, which means that the matching of upper case and lower case characters is dependent on the riles of the database's LC_CTYPE setting. Again, this behavior is identical to the use of lower in queries. but because it's done transparently by the data typoe, you don't have to remember to do anything special in your queries."
- Access data from Remote Database
SQL Server can pull data from remote database server using LINKEDSERVER and OPENQUERY or SQL Server Four Part Name whereas PostgreSQL can pull using Dblink but is less elegant and efficient than SQL Server.