Use SET DATABASE SQL SYNTAX PGS TRUE or the equivalent URL property sql.syntax_pgs=true to enable the PostgreSQL's non-standard features. References to SERIAL, BIGSERIAL, TEXT and UUID data types, as well as sequence functions, are translated into HyperSQL equivalents.

Use SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

PostgreSQL functions are generally supported.

For identity columns, PostgreSQL uses a non-standard linkage with an external identity sequence. In most cases, this can be converted to GENERATED BY DEFAULT AS IDENTITY. In those cases where the identity sequence needs to be shared by multiple tables, you can use a new HyperSQL 2.x feature GENERATED BY DEFAULT AS SEQUENCE <sequence name>, which is the equivalent of the PostgreSQL implementation.

In CREATE TABLE statements, the SERIAL and BIGSERIAL types are translated into INTEGER or BIGINT, with GENERATED BY DEFAULT AS IDENTITY. Usage of DEFAULT NEXTVAL(<sequence name>) is supported so long as the <sequence name> refers to an existing sequence. This usage is translated into GENERATED BY DEFAULT AS SEQUENCE <sequence name>.

In SELECT and other statements, the NEXTVAL(<sequence name>) and LASTVAL() functions are supported and translated into HyperSQL's NEXT VALUE FOR <sequence name> and IDENTITY() expressions.

PostgreSQL uses a non-standard expression, SELECT 'A Test String' to return a single row table. The standard form is VALUES('A Test String'). In PGS syntax mode, this type of SELECT is supported.

HyperSQL supports SQL Standard ARRAY types. PostgreSQL also supports this, but not entirely according to the Standard.

SQL routines are portable, but some syntax elements are different and require changes.

You may need to use SET DATABASE SQL TDC { DELETE | UPDATE } FALSE statements, as PostgreSQL does not enforce the subtle rules of the Standard for foreign key cascading deletes and updates. PostgreSQL allows cascading operations to update a field value multiple times with different values, the Standard disallows this.

With HyperSQL, an UPDATE statement can update UNIQUE and PRIMARY KEY columns of a table without causing an exception due to temporary violation of constraints. These constraints are checked at the end of execution, therefore there is no need for an ORDER BY clause in an UPDATE statement.

MySQL foreign key constraints are not enforced by the default MyISAM engine. Be aware of the possibility of data being rejected by HyperSQL due to these constraints.

With HyperSQL INSERT or UPDATE statements either succeed or fail due to constraint violation. MySQL has the non-standard IGNORE override to ignore violations and alter the data, which is not accepted by HyperSQL.

Unlike MySQL, HyperSQL allows you to modify a table with an INSERT, UPDATE or DELETE statement which selects from the same table in a subquery.

Use SET DATABASE SQL SYNTAX MYS TRUE or the equivalent URL property sql.syntax_mys=true to enable support for AUTO_INCREMENT and TEXT data types and several other types. These type definitions are translated into HyperSQL equivalents.

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

Avoid storing invalid values, for example invalid dates such as '0000-00-00' or '2001-00-00' which are rejected by HyperSQL.

Avoid the MySQL feature that trims spaces at the end of CHAR values.

In MySQL, a database is the same as a schema. In HyperSQL several schemas can exist in the same database and accessed transparently. In addition a HyperSQL server supports multiple separate databases.

In MySQL, older, non-standard, forms of database object name case-sensitivity make is difficult to port applications. Use the latest form which encloses case-sensitive names in double quotes.

MySQL functions are generally supported, including GROUP_CONCAT.

For fine control over type conversion, check the settings for SET DATABASE SQL CONVERT TRUNCATE FALSE

If you use concatenation of possibly NULL values in your select statements, you may need to change the setting with the SET DATABASE SQL CONCAT NULLS FALSE

If your application relies on MySQL behaviour for ordering of nulls in SELECT statements with ORDER BY, use both SET DATABASE SQL NULLS FIRST FALSE and SET DATABASE SQL NULLS ORDER FALSE to change the defaults.

MySQL supports most SQL Standard types (except INTERVAL types), as well as non-standard types, which are also supported by HyperSQL. Supported types include SMALLINT, INT, BIGINT, DOUBLE, FLOAT, DECIMAL, NUMERIC, VARCHAR, CHAR, BINARY, VARBINARY, BLOB, DATE, TIMESTAMP (all Standard SQL) and TINYINT, DATETIME (non Standard). UNSIGNED types are converted to signed.

MySQL uses a non-standard expression, SELECT 'A Test String' to return a single row table. The standard form is VALUES('A Test String'). In MYS syntax mode, this type of SELECT is supported.

Indexes defined inside CREATE TABLE statements are accepted and created. The index names must be unique within the schema.

HyperSQL supports ON UPDATE CURRENT_TIMESTAMP for column definitions.

HyperSQL translates INSERT IGNORE, REPLACE and ON DUPLICATE KEY UPDATE variations of INSERT into predictable and error-free operations.

When INSERT IGNORE is used, if any of the inserted rows would violate a PRIMARY KEY or UNIQUE constraint, that row is not inserted. The rest of the rows are then inserted only if there is no other violation such as long strings or type mismatch, otherwise the appropriate error is returned.

When REPLACE or ON DUPLICATE KEY UPDATE is used, the rows that need replacing or updating are updated with the given values. This works exactly like an UPDATE statement for those rows. Referential constraints and other integrity checks are enforced and update triggers are activated. The row count returned is simply the total number of rows inserted and updated.

MySQL user-defined function and procedure syntax is very similar to SQL Standard syntax. A few changes may still be required.

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

HyperSQL supports Java language functions and stored procedures with the standard syntax, which is similar to the way Derby supports these features.

Use SET DATABASE SQL SYNTAX ORA TRUE or the equivalent URL property sql.syntax_ora=true to enable support for some non-standard syntax of Oracle.

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

Fine control over MVCC deadlock avoidance is provided by the SET DATABASE TRANSACTION ROLLBACK ON CONFLICT FALSE and the corresponding hsqldb.tx_conflict_rollback connection property.

If your application relies on Oracle behaviour for nulls in multi-column UNIQUE constraints, use SET DATABASE SQL UNIQUE NULLS FALSE to change the default.

If your application relies on Oracle behaviour for ordering of nulls in SELECT statements with ORDER BY, but without NULLS FIRST or NULLS LAST, use both SET DATABASE SQL NULLS FIRST FALSE and SET DATABASE SQL NULLS ORDER FALSE to change the defaults.

If you use the non-standard concatenation of possibly NULL values in your select statements, you may need to change the setting for SET DATABASE SQL CONCAT NULLS FALSE.

Many Oracle functions are supported, including no-arg functions such as SYSDATE and SYSTIMESTAMP and more complex ones such as TO_DATE and TO_CHAR.

Non-standard data type definitions such as NUMBER, VARCHAR2, NVARCHAR2, BINARY_DOUBLE, BINARY_FLOAT, LONG, RAW are translated into the closest HyperSQL / SQL Standard equivalent in ORA mode.

Non-standard DEFAULT definitions for columns, such as the use of DUAL with a SEQUENCE function are supported and translated in ORA syntax mode.

The DATE type is interpreted as TIMESTAMP(0) in ORA syntax mode.

The DUAL table and the expressions, ROWNUM, CURRVAL, NEXTVAL are supported in ORA syntax mode.

HyperSQL natively supports operations involving datetime and interval values. These features are based on the SQL Standard.

Many subtle automatic type conversions, syntax refinements and other common features are supported.

SQL routines are generally portable, but some changes are required.

Use SET DATABASE SQL SYNTAX DB2 TRUE or the equivalent URL property sql.syntax_db2=true to enable support for some non-standard syntax of DB2.

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

HyperSQL supports almost the entire syntax of DB2 together with many of the functions. Even local temporary tables using the SESSION pseudo schema are supported.

The DB2 binary type definition FOR BIT DATA, as well as empty definition of column default values are supported in DB2 syntax mode.

Many DB2 functions are supported.

The DUAL table and the expressions, ROWNUM, CURRVAL, NEXTVAL are supported in DB2 syntax mode.

SQL routines are highly portable with minimal change.

Use SET DATABASE SQL SYNTAX MSS TRUE or the equivalent URL property sql.syntax_mss=true to enable support for the CONVERT(<type definition>, <expression) function with switched order of arguments

Use MVCC with SET DATABASE TRANSACTION CONTROL MVCC if your application is multi-user.

If you use the non-standard concatenation of possibly NULL values in your select statements, you may need to change the setting for SET DATABASE SQL CONCAT NULLS FALSE.

HyperSQL supports + for string concatenation.

SQLServer uses a non-standard expression, SELECT 'A Test String' to return a single row table. The standard form is VALUES('A Test String'). In MSS syntax mode, this type of SELECT is supported.

SQL routines need quite a lot of changes.