Let’s now inspect query syntax by implementing a number of pre-constructed SQL queries in MySQL and PostgreSQL.

Database Creation

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;

Table Creation

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.

PostgreSQL:

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

MySQL:

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

INSERT Statement

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

SELECT Statement

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;

UPDATE Statement

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

IDEs

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.