MYSQL vs PostgreSQL vs ORACLE - ByteScout
  • Home
  • /
  • Blog
  • /
  • MYSQL vs PostgreSQL vs ORACLE

MYSQL vs PostgreSQL vs ORACLE

MySQL, PostgreSQL, and Oracle are relational database management systems. The major difference between these three databases is that MySQL is open source whereas PostgreSQL is an open-source database management system and Oracle database is developed by Oracle corporation. These three databases support various programming languages. For instance, MySQL supports Java, C++, TCL, Perl, and Haskel. Oracle and PostgreSQL to support various programming languages.

Summary

  1. Trigger
  2. Index
  3. Backup
  4. Package
  5. Restore

Many database administrators think that the performance of MySQL is better than PostgreSQL. On the other hand, Oracle has more reliable recovery methods than MySQL. Oracle too supports various programming languages and it has robust recovery methods. The most significant thing is MySQL supports both UNIX and Linux. Oracle too supports Unix and Linux. The thing to remember is that all these three databases are active and robust and are widely used in the market. This post will explain the technical difference between these three databases.
MYSQL vs PostgreSQL vs ORACLE

Trigger

A trigger is a named block in any RDBMS. It is one of the most important database objects. For instance, Trigger is stored in the Database and fired implicitly when a triggering event occurs. In Oracle, MySQL, or PostgreSQL, the event can be DML, DDL, system event, or user event.

Oracle Trigger Example

In Oracle, Trigger can be used to implement multiple business practices that cannot be implemented using integrity constraints such as UNIQUE, NOT NULL, and CHECK. The following example is displaying the Trigger:

CREATE OR REPLACE TRIGGER clients_audit_tg
    AFTER 
    UPDATE OR DELETE 
    ON clients
    FOR EACH ROW    
DECLARE
   first_transaction VARCHAR2(20);
BEGIN
   -- find the transaction type
   first_transaction := CASE  
         WHEN UPDATING THEN 'UPDATE'
         WHEN DELETING THEN 'DELETE'
   END;

The above Trigger will be fired after a row in the table client is updated or deleted.

MySQL Trigger Example

MySQL Trigger will be activated when a specified event is executed for the table. The trigger can be executed for INSERT, UPDATE, and DELETE and it can be called before or after the event. The following example is displaying the Trigger.

CREATE TRIGGER before_emp_update 
    BEFORE UPDATE ON emp
    FOR EACH ROW 
 INSERT INTO emp_audit
 SET action = 'update',
     empno = OLD.empno,
     firstname = OLD.firstname,
     changedat = NOW();

PostgreSQL Trigger

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. The following example is displaying the Trigger.

First, create a new function called log_emp_name_changes:

CREATE OR REPLACE FUNCTION log_emp_name_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL  
  AS
$$
BEGIN
	IF NEW.emp_name <> OLD.emp_name THEN
		 INSERT INTO emp_audits(emp_id,emp_name,changed_on)
		 VALUES(OLD.id,OLD.emp_name,now());
	END IF;

	RETURN NEW;
END;
$$

Now, we will create a Trigger:

CREATE TRIGGER emp_name_changes
  BEFORE UPDATE
  ON emp
  FOR EACH ROW
  EXECUTE PROCEDURE log_empt_name_changes();

Index

In databases, an Index is a schema object that includes a record for each substance that arrives in the indexed column(s) of the table or cluster and gives immediate, quick access to rows. Oracle Database maintains several kinds of indexes: Normal indexes. (By default, Oracle Database creates B-tree indexes.) The concepts of Index in Oracle, MySQL, and PostgreSQL are the same but the syntaxes are different.

Oracle Index Example

In Oracle, to create an index in another schema, it is necessary to have the CREATE ANY INDEX system privilege. The following example is displaying how to create a simple index:

CREATE INDEX ord_client_ix_sample 
   ON sales (client_id, sales_id)
   COMPRESS 1;

The above command is displaying that the index will compress duplicated incidents of client_id column values.

MySQL Index Example

MySQL utilizes indexes to promptly get rows with particular column values. Without an index, MySQL browses the entire table to find the appropriate rows. The more extensive table, the slower it explores. The following example is displaying the MySQL index:

CREATE INDEX jobindex ON emp(jobTitle);

PostgreSQL Index Example

In PostgreSQL, An index is an ordered data structure that expedites the data retrieval on storage to support it. The following example is displaying the PostgreSQL index:

CREATE INDEX idx_phonebook_phone 
ON phonebook(phoneno);

Backup

Simply addressing, a database backup is a representational image of data. When the primary data is suffered, one can employ the backup to restore lost data (the real files that establish the database). This image copy covers significant parts of the database, such as the control file, archive logs, and datafiles-structures. In the case of a media fiasco, the database backup is the solution to favorably retrieving the data.

Backup in Oracle

The backup is the most important utility of Oracle. It belongs to the database administrator. One can utilize the BACKUP command to back up the main or a copy of a database, tablespace, or control file. It can also be used to take a backup of the server parameter file, archived redo log file, or backup set.

BACKUP DATABASE
  FORMAT '/disk5/arch_bps/db_%U.bck' 
  TAG monthly
  KEEP UNTIL TIME 'SYSDATE + 365'
  RESTORE POINT Q3FY09;

Backup in MySQL

In a MySQL database, if you use the backup utility then the data is derived as SQL statements. Hence, backing up and restoring large data can take a huge time because of the performance of various SQL statements. Moreover, to bypass inequality, MySQL secures the database throughout the backup method. Still, database administrators can revoke this by applying the mysqldump utility using a particular flag “:–single-transaction”. The following example is displaying the MySQL backup command.

# mysqldump -u root -ptecmint --all-databases > all-databases.sql

Backup in PostgreSQL

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. The following command is displaying the use of a backup command.

pg_dump testdb > testdb.bak

Package

Just like procedure, a package is also a schema object that gathers rationally associated variables, constants, subprograms, cursors, and exceptions. A package is collected and saved in the database. This is the main difference between Oracle, MySQL, and PostgreSQL. The package utility is only available in the Oracle database while it is not available in the MySQL or PostgreSQL databases.

A package always has a stipulation, which represents the public objects that can be called from outside the package. The following example is displaying how to create a package in Oracle.

Package in Oracle Example

CREATE OR REPLACE PACKAGE emppkg AS
  -- get employee's name
  FUNCTION emp_ename(empno NUMBER)
    RETURN VARCHAR2;
  -- get employee's salary
  FUNCTION emp_sal(empno NUMBER)
    RETURN NUMBER;
END emppkg;

CREATE OR REPLACE PACKAGE BODY emppkg AS
  -- get employee's fullname
  FUNCTION emp_ename(empno NUMBER) RETURN VARCHAR2 IS
      e_name VARCHAR2(60);
  BEGIN
    SELECT ename
    INTO e_name
    FROM emp
    WHERE emp_id = empno;

    RETURN e_name;

  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
  WHEN TOO_MANY_ROWS THEN
    RETURN NULL;
  END; 

  FUNCTION emp_sal(empno NUMBER) RETURN NUMBER IS
    emp_salary NUMBER(8,2);
  BEGIN
    SELECT sal
    INTO emp_salary
    FROM emp
    WHERE emp_id = empno;

    RETURN emp_salary;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN NULL;
      WHEN TOO_MANY_ROWS THEN
        RETURN NULL;
  END;
END emppkg;

Restore

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

Restore in Oracle

In Oracle, the RESTORE command is used to restore, verify, or test and preview RMAN backups. Usually, database administrators restore backups when a hardware/software malfunction has broken a prevailing datafile, control file, or archived redo log or before implementing a point-in-time restoration. This is not the proper time to be harsh, but recovery without backups is not recommended.

You understand that you should have examined your restoration plan and that you should regularly back up a damaged database before trying to restore it. In oracle, to restore a dynamic backup of a file is to build it and make it accessible to the Oracle database server. To redeem a reestablished datafile is to renew it by utilizing redo logs, that is, documents of modifications performed to the database after the backup was taken. The following example is displaying the use of a restore command.

RMAN> RESTORE ARCHIVELOG ALL DEVICE TYPE sbt PREVIEW;

The above command is the official example available in the Oracle documentation. The important thing here to note is that the RESTORE command restores complete backups with various parameters and database administrators can restore files to their default location or a separate location.

Restore in MySQL

In a MySQL database, if you use the backup utility then the data is derived as SQL statements. Hence, backing up and restoring large data can take a huge time because of the performance of various SQL statements. Moreover, to bypass inequality, MySQL secures the database throughout the backup method. Still, database administrators can revoke this by applying the “mysqldump utility using a particular flag “:–single-transaction“.

Restore in PostgreSQL

In PostgreSQL, it is essential to terminate all connections to that particular database before restoring and making the backup file. The psq 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. The following is the example of Restore in PostgreSQL.

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

About the Author

ByteScout Team ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.  
prev
next