PostgreSQL vs. MySQL – Syntax
Let’s now inspect query syntax by implementing a number of pre-constructed SQL queries in MySQL and PostgreSQL.
The front matter of a database in PostgreSQL can be specified at the time of its formation:
CREATE DATABASE Plants WITH ENCODING = 'UTF8';
In contrast to using char or VARCHAR in this way, MySQL is able to use a combination of the CHARACTER SET and COLLATE to check the encoding of a certain column:
CREATE DATABASE Plants CHARACTER SET utf8 COLLATE utf8_general_ci;
Creating a table based on a particular language is relatively similar to creating one with a typical dialect. PostgreSQL offers a built-in pseudotype called SERIAL, whereas MySQL uses AUTO_INCREMENT.
MySQL uses a combination of CONSTRAINT, REFERENCES, and FOREIGN KEY to connect a reference and a foreign table, while PostgreSQL uses a combination of REFERENCES and FOREIGN KEY.
CREATE TABLE Fruits ( id SERIAL PRIMARY KEY, name VARCHAR(25) UNIQUE, shape VARCHAR(25), color VARCHAR(25) DEFAULT 'Red', details INT REFERENCES FruitDetails(fruit_details_id) ) ;
CREATE TABLE Fruits ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(25) UNIQUE, shape VARCHAR(25), color VARCHAR(25) DEFAULT 'Red', details INT, CONSTRAINT fk_fruit_details FOREIGN KEY (fruit_details_id) REFERENCES FruitDetails(fruit_details_id) );
The INSERT statement is the same as in other SQL dialects:
INSERT INTO Fruits (name, shape, color) VALUES ('Apple', 'Round', 'Red'), ('Banana', 'Cylinder', 'Yellow'), ('Pear', 'Ovaloid', 'Yellow') ;
The same SELECT statement can be found in both MySQL and PostgreSQL:
SELECT id, name AS "Fruit Name", shape AS "Shape", color AS "Color" FROM Fruits LIMIT 10 OFFSET 5;
MySQL limit and the offset values can be separated with the use of commas:
SELECT id, name AS "Fruit Name", shape AS "Shape", color AS "Color" FROM Fruits LIMIT 5, 10;
Both dialects of the UPDATE statement are the same:
UPDATE Fruits SET color = 'Green' WHERE name = 'Apple';
Modifying a Column Type
It’s important to modify a column’s type by using the ALTER TABLE statement, and both. There are two types of UPDATEs in PostgreSQL. An ALTER_COLUMN modifies the original table and inserts a new:
ALTER TABLE Fruits ALTER COLUMN shape TYPE VARCHAR(30);
MODIFY COLUMN example:
ALTER TABLE Fruits MODIFY COLUMN shape VARCHAR(30);
Modifying a Column Default Value
Both PostgreSQL and MySQL require you to use ALTER TABLE and ALTER COLUMN to set or modify a default value:
ALTER TABLE Fruits ALTER COLUMN color SET DEFAULT 'Green';
There are a number of integrated development environments for PostgreSQL, but dbForge Studio is the most popular. If you use MySQL you can download MySQL Workbench.
Visual similarity of both IDEs is the database hierarchy is shown on the left. They look similar in the way that they’re laid out. On the upper screenshot you have the currently open SQL file with the queries, on the lower screenshot the result of those queries is shown.