PostgreSQL Compatibility

Database Creation Specifications

During MogDB database creation, the following PG compatibility mode is used:

create database dbnam DBCOMPATIBILITY='PG' encoding=’utf8’;

Data Type

Value Type

During development and usage, MogDB supports only the smallint, integer, bigint, numeric[(p[,s])], serial, and bigserial value types.

Type	PostgreSQL	MogDB	Storage Length	Remarks
tinyint	/	Supported	1 byte	0 to 255
smallint	Supported	Supported	2 bytes	-32,768 to +32,767
integer	Supported	Supported	4 bytes	-2,147,483,648 to +2,147,483,647
binary_integer	/	Supported	/	integer alias
bigint	Supported	Supported	8 bytes	-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807
decimal[(p[,s])]	Supported	Supported	Variable byte	A maximum of 131072 before the decimal point and 16383 after the decimal point
numeric[(p[,s])]	Supported	Supported	Variable byte	A maximum of 131072 before the decimal point and 16383 after the decimal point
number[(p[,s])]	/	Supported	/	Numeric alias
real	Supported	Supported	4 bytes	Accurate to six decimal digits
float4	/	Supported	4 bytes	Accurate to six decimal digits
double precision	Supported	Supported	8 bytes	Accurate to fifteen decimal digits
binary_double	/	Supported	8 bytes	Double precision alias
float8	/	Supported	8 bytes	Accurate to fifteen decimal digits
float[(p )]	/	Supported	4 or 8 bytes
dec[(p,[s])]	/	Supported	/	A maximum of 131072 before the decimal point and 16383 after the decimal point
integer[(p,[s])]	/	Supported	/	A maximum of 131072 before the decimal point and 16383 after the decimal point
smallserial	Supported	Supported	2 bytes	1 to 32,767
serial	Supported	Supported	4 bytes	1 to 2,147,483,647
bigserial	Supported	Supported	8 bytes	1 to 9,223,372,036,854,775,807
tinyint	/	Supported	1 byte	0 to 255

Character Type

During the development, MogDB supports only the char(n), varchar(n), and text character types.

Type	PostgreSQL	MogDB	Storage Length	Remarks
char(n)	Supported	Supported	A maximum of 1 GB in postgreSQL A maximum of 10 MB in MogDB	In postgreSQL, n indicates the number of characters. In MogDB, n indicates the number of bytes. In the compatibility PG mode, n indicates the number of characters.
nchar(n)	/	Supported	A maximum of 10 MB	n indicates the number of bytes. In the compatibility PG mode, n indicates the number of characters.
varchar(n)	Supported	Supported	A maximum of 1 GB in postgreSQL A maximum of 10 MB in MogDB	In postgreSQL, n indicates the number of characters. In MogDB, n indicates the number of bytes. In the compatibility PG mode, n indicates the number of characters.
varchar2(n)	/	Supported	A maximum of 10 MB	varchar(n) alias
nvarchar2(n)	/	Supported	A maximum of 10 MB	n indicates the number of characters.
text	Supported	Supported	1 GB - 1
clob	/	Supported	1 GB - 1	text alias

Time Type

During the development, MogDB supports only the timestamp[(p )][with time zone] and date time types.

Type	PostgreSQL	MogDB	Storage Length	Remarks
timestamp[(p )][without time zone]	Supported	Supported	8 bytes	4713 BC to 294276 AD
timestamp[(p )][with time zone]	Supported	Supported	8 bytes	4713 BC to 294276 AD
date	Supported	Supported	4 bytes	4713 BC to 5874897 AD (The actual storage size is 8 bytes in MogDB)
time[(p )][without time zone]	Supported	Supported	8 bytes	00:00:00 to 24:00:00
time[(p )][with time zone]	Supported	Supported	12 bytes	00:00:00+1459 to 24:00:00-1459
interval[fields][(p )]	Supported	Supported	16 bytes	-178000000 to 178000000 years
smalldatetime	/	Supported	8 bytes	Date and time without timezone, accurating to the minute, 30s equaling one minute
interval day(1) to second(p )	/	Supported	16 bytes
reltime	/	Supported	4 bytes

JSON Type

Type	PostgreSQL	MogDB	Storage Length	Remarks
json	Supported	Supported	/
jsonb	Supported	Supported since version 2.1	/

Keywords

In the following table, Reserved indicates that keywords in a database are reserved and cannot be customized. Non-reserved or N/A indicates that keywords can be customized.

Keyword	MogDB	PostgreSQL
AUTHID	Reserved	N/A
BUCKETS	Reserved	N/A
COMPACT	Reserved	N/A
DATE	Non-reserved (function or type is not supported)
DELTAMERGE	Reserved	N/A
EXCLUDED	Reserved	N/A
FENCED	Reserved	N/A
GROUPING		Non-reserved (function or type is not supported)
HDFSDIRECTORY	Reserved	N/A
IS	Reserved	Reserved (function or type is supported)
ISNULL	Non-reserved	Reserved (function or type is supported)
LATERAL		Reserved
LESS	Reserved	N/A
MAXVALUE	Reserved	Non-reserved
MINUS	Reserved	N/A
MODIFY	Reserved	N/A
NLSSORT	Reserved	N/A
NUMBER	Non-reserved (function or type is not supported)
PERFORMANCE	Reserved	N/A
PROCEDURE	Reserved	Non-reserved
REJECT	Reserved	N/A
ROWNUM	Reserved	N/A
SYSDATE	Reserved	N/A
VERIFY	Reserved	N/A

Implicit Conversion Comparison Table

Input Type	Target Type	MogDB	PG
bool	int2, int4, int8	Supported	int4 is not supported, others are the same
int2	bool, text, bpchar, varchar,interval	Supported (except bpchar)	NA
int4	bool, int2, text, bpchar, varchar, interval	Supported (except bpchar)	bool is not supported, int2 is in assignment, others are the same
int8	bool, text, bpchar, varchar	Supported (except bpchar)	NA
text	int8, int4, int2, float4, float8, date, timestamp, nemeric	Supported	NA
float4	int8, int4, int2, text, bpchar, varchar	Supported (except bpchar)	First three are in assignment, others are the same
float8	int8, int4, int2, text, float4, bpchar, varchar, interval, numeric	Supported (except bpchar)	int8, int4, int2, float4, numeric are in assignment, others are the same
bpchar	int8, int4, date, timestamp, numeric
date	text, bpchar, varchar	Supported (except bpchar)	NA
timestamp	text, varchar	Supported	NA
timestamptz	text	Supported	NA
numeric	int8, int4, int2, text, bpchar, varchar, interval	Supported (except bpchar)	First three are in assignment, others are the same

Issue