mysql vs postgresql code example

Example 1: mysql vs postgresql

/*Postgres is an object-relational database, while MySQL is a purely 
relational database. This means that Postgres includes features like
table inheritance and function overloading, which can be important
to certain applications. Postgres also adheres more closely to SQL
standards.

Postgres handles concurrency better than MySQL for multiple reasons:

Postgres implements Multiversion Concurrency Control (MVCC) without 
read locks Postgres supports parallel query plans that can use multiple
CPUs/cores Postgres can create indexes in a non-blocking way (through 
the CREATE INDEX CONCURRENTLY syntax), and it can create partial
indexes (for example, if you have a model with soft deletes, you
can create an index that ignores records marked as deleted) 
Postgres is known for protecting data integrity at the transaction 
level. This makes it less vulnerable to data corruption.*/

Example 2: data types mysql vs postgresql

MySQL                   PostgreSQL          SQLite

TINYINT                 SMALLINT            INTEGER
SMALLINT                SMALLINT
MEDIUMINT               INTEGER
BIGINT                  BIGINT
BIT                     BIT                 INTEGER
_______________________________________________________

TINYINT UNSIGNED        SMALLINT            INTEGER
SMALLINT UNSIGNED       INTEGER
MEDIUMINT UNSIGNED      INTEGER
INT UNSIGNED            BIGINT
BIGINT UNSIGNED         NUMERIC(20)
_______________________________________________________

DOUBLE                  DOUBLE PRECISION    REAL
FLOAT                   REAL                REAL
DECIMAL                 DECIMAL             REAL
NUMERIC                 NUMERIC             REAL
_______________________________________________________

BOOLEAN                 BOOLEAN             INTEGER
_______________________________________________________

DATE                    DATE                TEXT
TIME                    TIME
DATETIME                TIMESTAMP
_______________________________________________________

TIMESTAMP DEFAULT       TIMESTAMP DEFAULT   TEXT
NOW()                   NOW()   
_______________________________________________________

LONGTEXT                TEXT                TEXT
MEDIUMTEXT              TEXT                TEXT
BLOB                    BYTEA               BLOB
VARCHAR                 VARCHAR             TEXT
CHAR                    CHAR                TEXT
_______________________________________________________

columnname INT          columnname SERIAL   INTEGER PRIMARY 
AUTO_INCREMENT                              KEY AUTOINCREMENT