Differences between DBMS SQL dialects

This page reports a non exhaustive list of differences in the SQL language on some common DBMS.

Data

Date & Time

PostgreSQL always output timestamps in the PostgreSQL server current time zone. On MySQL, only the TIMESTAMP type will follow this behavir.

Query language (SELECT etc.)

ORDER BY columns

In a basic query, columns of an ORDER BY clause could be

• Any column of the main or joined table.
• A result column expression alias.

In a unioned query

• A column name appearing in the result columns
• A result column expression alias.

Result column aliases

• Result column aliases are not avaiable in WHERE and HAVING clauses except in SQLite.
• Result column aliases defined in UNIONed queries cannot be used in ORDER BY clause except in SQLite.

Data manipulation (INSERT, UPDATE, DELETE)

Default values in INSERT queries

When all columns of a INSERT query must use the DEFAULT column value, two syntax can be used depending on the DBMS.

INSERT INTO table DEFAULT VALUES

• SQLite
• PostgreSQL

INSERT INTO table (column) VALUES (DEFAULT)°

• PostgreSQL
• MySQL

Data structure language (CREATE, DROP)

Expressions in DEFAULT column constraint

SQLite

Supported in DEFAULT constraints using surrounding parenthesis.

References

• Documentation

MySQL

Supported using triggers

CREATE TRIGGER trigger_name 
	BEFORE INSERT ON table 
	FOR EACH ROW 
		SET new.column = uuid();

References

• StackOverflow

MariaDB

Supported since 10.2.1

References

• Documentation

PostgreSQL

Supported using a trigger calling a stored procedure.

CREATE OR REPLACE FUNCTION t_insert_proc() RETURNS TRIGGER AS 
	'BEGIN 
		new.text = now(); 
		RETURN new;
	END;'
	LANGUAGE 'plpgsql';
 			
 	CREATE OR REPLOACE TRIGGER t_insert_text 
 		BEFORE INSERT 
 		ON t 
 		FOR EACH ROW 
 			EXECUTE PROCEDURE t_insert_proc();

References

• StackOverflow

Temporary objects

Depending on DBMS, temporary objects may resides in the global scope (SQLite), a special namespace (PostgreSQL) or in any namespace (MySQL).