默认数据类型映射
PostgreSQL与虚谷数据库的默认数据类型映射如表1所示。
| PostgreSQL数据类型 | PostgreSQL列类型 | Xugu数据类型 | Xugu列类型 |
|---|---|---|---|
| SMALLINT | SMALLINT | SMALLINT | SMALLINT |
| INT INTEGER | INTEGER | INTEGER | INTEGER |
| BIGINT | BIGINT | BIGINT | BIGINT |
| DECIMAL[(M[,D])] NUMERIC[(M[,D])] | NUMERIC | NUMERIC | NUMERIC |
| REAL | REAL | FLOAT | FLOAT |
| DOUBLE PRESICION | DOUBLE PRESICION | DOUBLE | DOUBLE |
| SMALLSERIAL SERIAL BIGSERIAL | SAMLLINTRGER INTRGER BIGINTRGER | INTEGER identity(1,1) INTEGER identity(1,1) BIGINTEGER identity(1,1) | INTEGER |
| CHARACTER(M) CHAR(M) | CHARACTER | CHARACTER(M) CHAR(M) | CHAR |
| CHARACTER VARYING(M) VARCHAR(M) | CHARACTER VARYING | CHARACTER VARYING(M) VARCHAR(M) | VARCHAR |
| TEXT | TEXT | CLOB | CLOB |
| BYTEA | BYTEA | BINARY | BINARY |
| TIMESTAMP[(M)] [WITHOUT TIME ZONE] | TIMESTAMP WITHOUT TIME ZONE | TIMESTAMP | TIMESTAMP |
| TIMESTAMP[(M)] WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | TIMESTAMP[(M)] WITH TIME ZONE | DATETIME WITH TIME ZONE |
| DATE | DATE | DATE | DATE |
| TIME[(M)] [WITHOUT TIME ZONE] | TIME WITHOUT TIME ZONE | TIME | TIME |
| TIME[(M)] WITH TIME ZONE | TIME WITH TIME ZONE | TIME[(M)] WITH TIME ZONE | TIME WITH TIME ZONE |
| INTERVAL | INTERVAL | INTERVAL | INTERVAL |
| BOOLEAN | BOOLEAN | BOOLEAN | BOOLEAN |
| ENUM | ENUM | ||
| BIT[(M)] | BIT | ||
| BIT VARYING[(M)] | BIT VARYING | ||
| TSVECTOR | TSVECTOR | ||
| TSQUERY | TSQUERY | ||
| UUID | UUID | GUID | GUID |
| XML | XML | ||
| JSON | JSON |
PostgreSQL数据类型的特殊值：
- numeric类型数据可以插入NaN字符串，带单引号插入，含义为“不是一个数”。
- real、double precision类型数据可以插入NaN、infinity、 -infinity字符串，带单引号插入，含义分别为 “不是一个数”、“正无穷大”、“负无穷大”。
- 日期/时间类型插入特殊字符串。
输入字符串 适用类型 描述 epoch date, timestamp 1970-01-01 00:00:00+00 (Unix 系统零时) infinity date, timestamp 比任何其它时间戳都晚 -infinity date, timestamp 比任何其它时间戳都早 now date, time, timestamp 当前事务的开始时间 today date, timestamp 今日午夜 tomorrow date, timestamp 明日午夜 yesterday date, timestamp 昨日午夜 allballs time 00:00:00.00 UTC
PostgreSQL的序列号类型（smallserial、serial、bigintserial）不是真正的类型，是一个逻辑类型，实质上就是INTEGER类型加了默认自增值，INTEGER NOT NULL DEFAULT nextval（'tablename_colname_seq'），其中small和big分别对应INTEGER的small与big。