Switching from PostgreSQL to MS SQL Server

What are the differences in PostgreSQL vs. MS SQL Server? We discuss the similarities and the differences between these two popular SQL dialects.

Many of us developers and database specialists began working with free relational databases like PostgreSQL or MySQL for personal projects or small organizations. Some of us have since moved on to organizations using MS SQL Server and have had to learn to use the new tool.

You may be wondering how challenging that is and whether you would struggle with SQL Server under similar circumstances. In this article, we discuss whether it is difficult to switch from PostgreSQL to MS SQL Server.

If you want to become an SQL master, the track SQL from A to Z on LearnSQL.com is highly recommended. It gives you everything you need to know to work with databases and analyze data efficiently.

A Byte of History

Michael Stonebraker is an American computer scientist specializing in database systems. He first developed Ingres, a proprietary SQL relational database management system, at the University of California, Berkeley.

In 1985, he began working on Postgres, an evolved version of Ingres with complete data type support and relationships. The development team later incorporated SQL support in the project, and in 1996, Postgres became PostgreSQL.

Its first formal version was released in 1997. The project has since evolved with several significant changes over its lifetime. According to Stack Overflow, PostgreSQL is one of the most popular database systems, far ahead of SQL Server.

For more on the history of PostgreSQL, read this detailed article on LearnSQL.com.

Microsoft SQL Server, also known as MS SQL Server or simply SQL Server, is a relational database management system developed by Microsoft. Its first version was SQL Server v1.0, a 16-bit relational database for the OS/2 operating system.

Released in 1989, MS SQL Server was meant to compete with Oracle, IBM, Informix, and Ingres, among others. It has evolved with many rewrites and modifications to get to its current version, SQL Server 2019.

If you want to know more about the history of MS SQL Server, read this detailed article on LearnSQL.com.

PostgreSQL vs. MS SQL Server

Both PostgreSQL and MS SQL use the standard SQL query language. However, each database system uses its own version of the SQL language, usually called SQL dialect. In linguistics, dialects are generally mutually comprehensible. Does this apply to PostgreSQL vs. MS SQL Server?

Data Types

Both PostgreSQL and SQL Server use the basic SQL data types like INTEGER, TEXT, and VARCHAR, though with slight differences. Here is an overview of the main differences in data types between PostgreSQL vs. SQL Server:

• The Boolean type does not exist in SQL Server. The BIT type is used instead. According to Microsoft documentation: “The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1, and FALSE is converted to 0.”
• The Integer type is called INT in both SQL Server and PostgreSQL. However, PostgreSQL also accepts the aliases INTEGER and INT4.
• To manage date and time separately, both PostgreSQL and SQL Server provide the types DATE and TIME. However, the data type to manage date and time together is DATETIME in SQL Server, whereas the same is TIMESTAMP in PostgreSQL.
• To manage floating numbers, PostgreSQL uses the types REAL and DOUBLE PRECISION (4 and 8 bytes, respectively) while SQL Server uses REAL and FLOAT.
• Both PostgreSQL and SQL Server manage the type SMALLINT for signed two-byte integers, but SQL Server further provides the type TINYINT for one-byte integers.

PostgreSQL Syntax vs. MS SQL Server Syntax

Now, let’s check the query syntax. We analyze common SQL statements and check the differences between the SQL dialects.

Database Creation

In PostgreSQL, the character encoding may be specified during the creation of the database, like this:

CREATE DATABASE Plants
WITH
   ENCODING = 'UTF8';

Instead of specifying the character encoding, SQL Server uses collations with character encoding as a suffix:

CREATE DATABASE Plants
    COLLATE Latin1_General_100_CI_AI_SC_UTF8;

Table Creation

Table creation in PostgreSQL vs. MS SQL Server is pretty similar. However, PostgreSQL provides the pseudotype SERIAL for creating unique identifier columns while SQL Server uses IDENTITY.

There is also a difference in foreign keys: the statement to connect a column and a foreign table is REFERENCES in PostgreSQL whereas the same is FOREIGN KEY REFERENCES in SQL Server, as shown below:

In PostgreSQL:

CREATE TABLE Fruits
    (
      id SERIAL PRIMARY KEY,
      name VARCHAR(25) UNIQUE,
      shape VARCHAR(25),
      color VARCHAR(25) DEFAULT 'Red',
      details INT REFERENCES FruitDetails(fruit_details_id)
    )
;

In SQL Server:

CREATE TABLE Fruits
    (
      id INT IDENTITY(1,1) PRIMARY KEY,
      name VARCHAR(25) UNIQUE,
      shape VARCHAR(25),
      color VARCHAR(25) DEFAULT 'Red',
      details INT FOREIGN KEY REFERENCES FruitDetails(fruit_details_id)
    )
;

Insert Statement

The INSERT statement is identical in both SQL dialects:

INSERT INTO Fruits
    (name, shape, color)
VALUES
    ('Apple', 'Round', 'Red'),
    ('Banana', 'Cylinder', 'Yellow'),
    ('Pear', 'Ovaloid', 'Yellow')
;

Select Statement

The SELECT statement is identical in PostgreSQL vs. MS SQL Server:

SELECT
  id,
  name AS "Fruit Name",
  shape AS "Shape",
  color AS "Color"
FROM
  Fruits;

However, SQL Server also allows the use of the brackets for column aliases:

SELECT
  id,
  name AS [Fruit Name],
  shape AS [Shape],
  color AS [Color]
FROM
  Fruits;

Update Statement

The UPDATE statement is identical in both SQL dialects:

UPDATE Fruits
SET
  color = 'Green'
WHERE
  name = 'Apple';

Modifying the Column Type

To modify a column type, the ALTER COLUMN statement may be used. However, there is a slight difference between PostgreSQL vs. MS SQL Server.

In PostgreSQL, the query to modify a column type is the following:

ALTER TABLE Fruits
ALTER COLUMN shape TYPE VARCHAR(30)

In SQL Server, mentioning TYPE is not necessary:

ALTER TABLE Fruits
ALTER COLUMN shape VARCHAR(30)

Modifying the Column Default Value

To modify a default column value in PostgreSQL, the column must be altered first. Then, the default value may be set.

ALTER TABLE Fruits
ALTER COLUMN color
SET DEFAULT 'Green';

In SQL Server, a constraint to the table must be created first. Then, the default value for the column may be defined.

ALTER TABLE Fruits
ADD CONSTRAINT default_color
DEFAULT 'Green' FOR color;

An Overview of the IDEs

There are plenty of IDEs for PostgreSQL; the most popular is pgAdmin. For MS SQL Server, the official IDE is SQL Server Management Studio (SSMS).

Both IDEs are visually similar: the database hierarchy is shown to the left with tables and views. The center part shows the currently open SQL file with its queries, while the bottom part shows the result of the queries.

PostgreSQL vs. MS SQL Server: There Is Nothing to Fear!

In this article, we have shown that the differences between PostgreSQL vs. MS SQL Server are minuscule. Both are SQL dialects, but they are still SQL languages.

So, don’t be afraid! If you have to switch from using PostgreSQL to using MS SQL Server, the learning curve is relatively short. With previous knowledge of the SQL standard, taking a quick look at each technical documentation clarifies those differences.

If you need a refresher on SQL queries, take a look at the excellent SQL Basics course on LearnSQL.com.