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.
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';
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
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') );
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
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