DEV Community

Cong Li
Cong Li

Posted on

GBase 8c Compatibility Mode Usage Guide

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
Enter fullscreen mode Exit fullscreen mode

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:

  1. This parameter must be specified when creating the database and cannot be modified later through SQL statements.
  2. 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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Image description

2. PostgreSQL Compatibility

Note:

  1. 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;
Enter fullscreen mode Exit fullscreen mode

Image description

3. MySQL Compatibility

Note:

  1. 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
Enter fullscreen mode Exit fullscreen mode

Image description

Additionally, GBase 8c in MySQL compatibility mode supports MySQL built-in functions:

  • LEAST(expr1, expr2, expr3, …) returns the smallest value in the list.

Image description

  • LOG(x) returns the natural logarithm (base e) of x.

Image description

  • POW(x, y) / POWER(x, y) returns x raised to the power of y.

Image description

  • CONCAT(s1, s2, …, sn) concatenates multiple strings s1, s2, etc., into one string.

Image description

  • FIND_IN_SET(s1, s2) returns the position of the string s1 in the string s2.

Image description

  • FORMAT(x, n) formats the number x to " #,###.##", rounding to n decimal places.

Image description

Top comments (0)