瀚高数据库
目录
环境
文档用途
详细信息
环境
系统平台：Microsoft Windows (64-bit) 10
版本：4.5
文档用途
介绍瀚高数据库中创建一个datetime类型的方法以及create domain 和create type的用法和区别。
详细信息
瀚高数据库中支持使用以下语句创建用户定义的数据类型：
CREATE DOMAIN：它创建了一个用户定义的数据类型，可以有可选的约束，基于其他基本类型，实质是定义一个域。
CREATE TYPE：它通常用于使用存储过程创建复合类型(两种或多种数据类型混合的数据类型)。
domain用法及示例
假如有以下表结构：
create table test_domain (id varchar,md5 text not null check(length(md5)=32));
其中md5列的类型及约束，可以定义一个domain来抽象，如下：
highgo=# create domain md5 ashighgo-# text not nullhighgo-# check (highgo(#length(value) = 32highgo(# );CREATE DOMAINhighgo=# highgo=# \dD md5List of domainsSchema | Name | Type | Collation | Nullable | Default | Check --------+------+------+-----------+----------+---------+----------------------------public | md5 | text | | not null | | CHECK (length(VALUE) = 32)(1 row)highgo=# create table test_domain (id varchar,md5 md5);CREATE TABLEhighgo=# insert into test_domain values('1','2');ERROR: value for domain md5 violates check constraint "md5_check"highgo=# insert into test_domain values('2','76a2173be6393254e72ffa4d6df1030a');INSERT 0 1
创建MySQL中datetime类型
highgo=# create domain datetime as timestamp without time zone;highgo=# create table t_time (id int,create_time datetime);CREATE TABLEhighgo=# \d+ t_timeTable "public.t_time"Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+----------+-----------+----------+---------+---------+--------------+-------------id| integer | || | plain | | create_time | datetime | || | plain | | Access method: heaphighgo=# insert into t_time values (1,now()),(2,now());INSERT 0 2highgo=# highgo=# select * from t_time;id | create_time ----+----------------------------1 | -08-03 19:28:11.2073242 | -08-03 19:28:11.207324(2 rows)
create type用法及示例
CREATE TYPE name AS( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )CREATE TYPE name AS ENUM( [ 'label' [, ... ] ] )CREATE TYPE name AS RANGE (SUBTYPE = subtype[ , SUBTYPE_OPCLASS = subtype_operator_class ][ , COLLATION = collation ][ , CANONICAL = canonical_function ][ , SUBTYPE_DIFF = subtype_diff_function ])CREATE TYPE name (INPUT = input_function,OUTPUT = output_function[ , RECEIVE = receive_function ][ , SEND = send_function ][ , TYPMOD_IN = type_modifier_input_function ][ , TYPMOD_OUT = type_modifier_output_function ][ , ANALYZE = analyze_function ][ , INTERNALLENGTH = {internallength | VARIABLE } ][ , PASSEDBYVALUE ][ , ALIGNMENT = alignment ][ , STORAGE = storage ][ , LIKE = like_type ][ , CATEGORY = category ][ , PREFERRED = preferred ][ , DEFAULT = default ][ , ELEMENT = element ][ , DELIMITER = delimiter ][ , COLLATABLE = collatable ])CREATE TYPE name
创建示例：
CREATE TYPE compfoo AS (f1 int, f2 text);CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$SELECT fooid, fooname FROM foo$$ LANGUAGE SQL;CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');CREATE TABLE bug (id serial,description text,status bug_status);CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
