GBase 8c Compatibility Mode Usage Guide
Tags: database
Published: 2024-07-10 08:54:32

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

```sql
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:**
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:

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

## 1. Oracle Compatibility

```sql
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;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ed6u1jnwaozsm7laslzg.png)

## 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.

```sql
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;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ldc795116d474x6yhdy.png)

## 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.

```sql
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
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jtsx5uru2p81fpva7l1o.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j3o4ccv81v3guravfm7q.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xn6v8tlu0cilbncc9i4j.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cmdmds9loutrmo3pt5mg.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w3i0bfuh7ycfst8zl9c0.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8k1y20xtpsibk43hcd53.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rha6f8hk4b9qdlilxlob.png)