Home
/
Blog
/
MSSQL vs PostgreSQL Features : Complete Tutorial

MSSQL vs PostgreSQL Features : Complete Tutorial

MSSQL and PostgreSQL are both relational databases. They both are identical but different in various ways. They both are created to store data in an organized way for querying. One of the major differences between both is that PostgreSQL is an open-source database management system and MSSQL is a relational database management system of Microsoft.that is most popular with mid-sized and large enterprises. Both these databases are widely used by many enterprises for creating robust applications. This article will describe the technical difference between both databases.

MSSQL Create Database Vs PostgreSQL Create Database

The create is the common command in any relational database management system. In MSSQL and PostgreSQL this create command can be used to create a database, table functions, stored procedures, and many important things. In this example, the create command is used to create databases. In PostgreSQL, the program createdb is a wrapper application already given just like MSSQL UI.

MSSQL Create Database Example

USE master;
GO
--Detach the MyTest database
sp_detach_db MyTest;
GO
CREATE DATABASE MyTest ON
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\MyTest_data.mdf'),
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\MyTest_log.ldf'),
    (FILENAME = 'c:\myFiles\ImpFiles')
FOR ATTACH;
GO

PostgreSQL Create Database Example

CREATE DATABASE db_name
 ENCODING 'UTF8'
 LC_COLLATE 'en_US.UTF-8'
 LC_CTYPE 'en_US.UTF-8';

MSSQL Stored Procedure Vs. PostgreSQL Function

A stored procedure is a saved SQL code. The stored procedure is the most important element of a relational database management system. In MSSQL, users can write T-SQL code and save that code as a stored procedure. This code or stored procedure can be called or reused again and again. It is like automation of a particular task. You just have to enter values in the parameter and then it will be executed automatically to complete the given task.

PostgreSQL enables users to increase database operations with user-defined functions. This is done by using different procedural languages, which are usually known as stored procedures. In PostgreSQL, users can create their stored functions and recall them whenever required.

MSSQL Stored Procedure Example

USE TestDB;  
GO  
CREATE PROCEDURE MyProce.uspGetEmpTestDB2   
    @LName nvarchar(70),   
    @FName nvarchar(70)   
AS   

    SET NOCOUNT ON;  
    SELECT FName, LName, Dept 
    FROM MyProce.vEmp 
    WHERE FName = @FName AND LName = @LName  
    AND EndDate IS NULL;  
GO

PostgreSQL Function Example

CREATE OR REPLACE FUNCTION my_function(IN a int,IN b int, OUT add int, OUT pr int) AS $$
BEGIN
    IF x < 5 THEN
      RAISE WARNING 'information message %', now();
      RAISE NOTICE 'information message %', now();
      RAISE INFO 'information message %', now();
    END IF;
    sum := a + b;
    pr := a * b;
END;
$$ LANGUAGE plpgsql

MSSQL Triggers vs PostgreSQL Triggers

In any relational database management system, Trigger is just like a stored procedure that is event-driven. In MSSQL, triggers are special stored procedures that are implicitly or explicitly fired automatically in reply to a particular event, object, database, and other server events. MSSQL contains three types of triggers. These are Data manipulation language (DML) triggers, Data definition language (DDL) triggers, and Logon triggers.

In PostgreSQL, the trigger is also one special function. This special function is called automatically whenever an insert, update, and delete events occur. These are user-defined triggers. PostgreSQL gives two main triggers: row and statement-level triggers. Users can define whether the trigger is called before or after an event. If the trigger is called before an event, it can jump the process for the row or even switch the row. If a trigger is called after the event, all modifications are open to the trigger.

MSSQL Trigger Example

CREATE TABLE prod.audits(
    item_id INT IDENTITY PRIMARY KEY,
    item_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    brand_number INT NOT NULL,
    category_number INT NOT NULL,
    model SMALLINT NOT NULL,
    li_price DEC(10,2) NOT NULL,
    modified_at DATETIME NOT NULL,
    operation CHAR(3) NOT NULL,
    CHECK(operation = 'INS' or operation='DEL')
);

MSSQL Backup Vs PostgreSQL Backup

Backup is the most important utility of any relational database. If data is lost then everything is lost. One can say that backup is the oxygen of a relational database management system. Both MSSQL and PostgreSQL provide the backup utility that can be used to take a backup of the database and then it can also be used to restore the database.

MSSQL allows database administrators to take a complete backup of a SQL Server database. It also allows us to create a database backup or filegroup. The full recovery model backs up the transaction log of the database to create a log backup. This is also called a bulk-logged recovery model.

On the other hand, the PostgreSQL database allows users to take backup using a utility. It contains the pg_dump utility to analyze backing up a separate database. This command is fired as a user with reading permissions. The backup utility comes under the database administrator command.

MSSQL Backup command

BACKUP DATABASE MyDB2020
TO DISK='A:\SQLServerBackups\MyDB1.bak',
DISK='B:\SQLServerBackups\MyDB2.bak',
DISK='C:\SQLServerBackups\MyDB3.bak'
WITH FORMAT,
  MEDIANAME = 'MyDB2020StripedSet0',
  MEDIADESCRIPTION = 'Striped media set for MyDB2020 database';
GO

PostgreSQL Backup command

pg_dump testdb > testdb.bak

Restore in MSSQL Vs Restore in PostgreSQL

Just like backup, the restore is one of the most valuable commands of any relational database management system. If you are working as a database administrator then backup and restore are two commands that are widely used in the database environment. If you have taken a backup of the database and now if you don’t know how to restore it then it is not a sign of a good database administrator.

In MSSQL, a restore situation is the method of restoring data or database from one or more backup files and then retrieving the database. The restore situations depend on the recovery pattern of the database. It is the method of copying data from a backup and using logged events to the data. Restore is what database administrators perform with backups. They always take the backup file and transfer it into a database.

In PostgreSQL, it is essential to terminate all connections to that particular database before restoring and making the backup file. The psql utility enables users to restore SQL script files created by the pg_dump, pg_dumpall utilities. These utilities are responsible for generating suitable backed up files.

MSSQL Restore Example

RESTORE DATABASE MyDB2020
  FROM MyDB2020Backups;

PostgreSQL Restore Example

>pg_restore --dbname=mytestdb_tpl --section=pre-data  c:\mybackup\mytestdb.tar