介绍瀚高数据库中创建一个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 as highgo-# text not null highgo-# check ( highgo(# length(value) = 32 highgo(# ); CREATE DOMAIN highgo=#
highgo=# \dD md5 List of domains Schema | 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 TABLE highgo=# 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 TABLE highgo=# \d+ t_time Table "public.t_time" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -------------+----------+-----------+----------+---------+---------+--------------+------------- id | integer | | | | plain | | create_time | datetime | | | | plain | | Access method: heap highgo=# insert into t_time values (1,now()),(2,now()); INSERT 0 2 highgo=# highgo=# select * from t_time; id | create_time ----+---------------------------- 1 | 2021-08-03 19:28:11.207324 2 | 2021-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); |
编辑推荐:
- 创建一个MySQL数据库中的datetime类型03-01
- MySQL 8.0新特性-并行查询innodb_parallel_read_threads03-01
- 关于MySQL中某个操作影响行数为0的理解03-01
- 后台执行SQL语句(oracle)03-01
- Mysqldump的备份流程03-01
- MySQL information_schema.columns表查询慢原因分析03-01
- 在容器环境搭建mysql备库03-01
- mysql 并发复制03-01
相关推荐
-
雷神推出 MIX PRO II 迷你主机:基于 Ultra 200H,玻璃上盖 + ARGB 灯效
2 月 9 日消息,雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C:支持手写笔、第三方安卓应用
2 月 10 日消息,制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- MySQL 8.0新特性-并行查询innodb_parallel_read_threads
- 关于MySQL中某个操作影响行数为0的理解
关于MySQL中某个操作影响行数为0的理解
26-03-01 - Mysqldump的备份流程
Mysqldump的备份流程
26-03-01 - MySQL information_schema.columns表查询慢原因分析
- 冬季实战营 动手实战-MySQL数据库快速部署实践 领鼠标 云小宝
冬季实战营 动手实战-MySQL数据库快速部署实践 领鼠标 云小宝
26-03-01 - Mysql Key Buffer Size
Mysql Key Buffer Size
26-03-01 - 在Rainbond中实现数据库结构自动化升级
在Rainbond中实现数据库结构自动化升级
26-03-01 - 数据库对比系列之二(MySQL和达梦)
数据库对比系列之二(MySQL和达梦)
26-03-01 - 《MySQL 性能优化》之理解 MySQL 体系结构
《MySQL 性能优化》之理解 MySQL 体系结构
26-03-01 - 举例解释一下MySQL的表锁和行锁
举例解释一下MySQL的表锁和行锁
26-03-01
