GBase 8c Compatibility Mode Usage Guide

#database

To address the challenges commonly faced during homogeneous/heterogeneous database migrations, GBase 8c optimizes design from multiple perspectives, including database compatibility and supporting tools. Built on the foundation of adaptability and performance in the core, GBase 8c is compatible with various relational databases such as Oracle, PostgreSQL, MySQL, and Teradata, providing comprehensive SQL support and a rich function library. Below is a brief introduction to the relevant syntax for commonly used relational databases.

Database-Level Compatibility

DBCOMPATIBILITY [ = ] compatibility_type

Specifies the type of database compatibility, with a default compatibility of 'O'.

Values: A, B, C, PG. These represent compatibility with Oracle, MySQL, Teradata, and PostgreSQL, respectively.

Note:

This parameter must be specified when creating the database and cannot be modified later through SQL statements.
In A mode, the database treats empty strings as NULL, and the DATE data type is replaced by TIMESTAMP(0) WITHOUT TIME ZONE.

Example:

CREATE DATABASE database_name WITH ENCODING = 'UTF8' DBCOMPATIBILITY = 'A' OWNER username;

1. Oracle Compatibility

CREATE DATABASE oracle WITH ENCODING = 'UTF8' DBCOMPATIBILITY = 'A' OWNER test;

CREATE TABLE users (
  id NUMBER PRIMARY KEY,
  username VARCHAR2(50) NOT NULL,
  email VARCHAR2(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX idx_users_username ON users(username);

INSERT INTO users VALUES (1, '张三', '11111@qq.com');
SELECT * FROM users;

2. PostgreSQL Compatibility

Note:

In PG mode, CHAR and VARCHAR count by characters, while other compatibilities count by bytes. For example, in the UTF-8 character set, CHAR(3) can store 3 Chinese characters in PG compatibility, but only 1 Chinese character in other compatibilities.

CREATE DATABASE pg WITH ENCODING = 'UTF8' DBCOMPATIBILITY = 'PG' OWNER test;

CREATE TABLE postgres (
  id INT PRIMARY KEY,
  data VARCHAR(100)
);

CREATE SEQUENCE postgres_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER TABLE postgres ALTER COLUMN id SET DEFAULT nextval('postgres_id_seq');

SELECT nextval('postgres_id_seq');
-- nextval: 1

SELECT nextval('postgres_id_seq');
-- nextval: 2

INSERT INTO postgres(data) VALUES ('11acb'), ('222ABC');
SELECT * FROM postgres;

3. MySQL Compatibility

Note:

When converting strings to integer types, if the input is invalid, B compatibility will convert the input to 0, whereas other compatibilities will throw an error.

CREATE DATABASE mysql WITH ENCODING = 'UTF8' DBCOMPATIBILITY = 'B' OWNER test;
\c mysql

CREATE TABLE mytable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO mytable (name) VALUES ('John');
SELECT LAST_INSERT_ID();
-- last_insert_id: 1

SELECT * FROM mytable;
-- id | name
--  1 | John