Technical Similarities
- use SQL (Structured Query Language)
- are relational databases
- with schemas
- tables (similar format)
- familiar MySQL data-types
- queries
- CLI (Command Line Interface)
Technical Differences
- Data type variations: BYTEA instead of BLOB, TEXT instead of LONGTEXT and MEDIUMTEXT, INTEGER sizing, UUID in postgres not in mysql
- CLI syntax
Postgres object-relational features (ORDBMS):
*act as a middle man between object oriented databases (info represented by objects) and relational databases / couples well with an object oriented programming language like Java, C++, Ruby, Python
- table inheritance[1]
- function creation / overloading[2]
- foreign data wrappers
- others*: more than one schema per database, ACID compliance[3], materialized views,constraint checking, lateral joins, variadic arguments, etc.
I recommend checking out Dian Fay's Ultimate Blog Post here for more detail:(https://dev.to/dmfay/the-ultimate-postgres-vs-mysql-blog-post-1l5f)
Installing
(linux commands)
PostgreSQL: sudo apt install postgresql-10
MySQL: sudo apt install mysql-server
Starting
PostgreSQL: sudo -u postgres psql
MySQL: sudo mysql -u root
User Roles
PostgreSQL: default user = ‘postgres’, password needs to be set
MySQL: default user = ‘root’, password ''
Altering Roles
PostgreSQL:
ALTER USER postgres with encrypted password 'postgres';
MySQL:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘newpassword’;
Running a schema file
PostgreSQL: sudo -u postgres psql -f [file.sql]
MySQL: sudo mysql -u root < [file.sql]
Common CLI commands
PostgreSQL:
list databases: \l
connect: \c [database_name]
list tables: \dt or \dt+
describe table: \d [tablename];
MySQL:
list databases: show databases;
connect: use [database_name];
list tables: show tables;
describe table: describe [tablename];
Tables structure is the same!
*data-type usage may be different, tab formatting may vary
PostgreSQL:
CREATE TABLE users (
id INT GENERATED ALWAYS AS IDENTITY,
username VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);
MySQL:
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
PRIMARY KEY (id)
);
Raw queries are basically the same
Still SQL!
PostgreSQL: SELECT * FROM tablename
MySQL: SELECT * FROM tablename
Quitting
PostgreSQL: \q
MySQL: quit; OR exit;
PostgreSQL or MySQL?
Consider if you'll need access to PostgreSQL's added Object Oriented features. Think about how much you value open source. PostgreSQL is truly open source, whereas MySQL is only 'partially' open source. Figure out how you'll install, open your CLI, run schema file and then you'll use PostgreSQL almost the same way you'd use MySQL with some altered command lines, added functionality, and modified data-types. You can ignore any PostgreSQL features you don't need and use them if and when you need them.
[1]TABLE INHERITANCE: Let’s say you are storing major cities in the US. You have a table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. a row of capitals inherits all columns (name, population, and altitude) from its parent, cities
[2]FUNCTION OVERLOADING: Postgres can store functions in its database and more than one function may be defined with the same name, so long as the arguments they take are different. This renaming is called overloading. The one matching the argument datatype will be the stored function called and evaluated.
[3]ACID: (Atomicity, Consistency, Isolation, Durability) refers to a standard set of properties that guarantee database transactions are processed reliably. ... An ACID-compliant DBMS ensures that the data in the database remains accurate and consistent despite any such failures
Top comments (0)