学习目标

学习openGauss定义数据类型

1.创建类型

–创建一种复合类型

omm=# CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE
omm=# CREATE TABLE t1_compfoo(a int, b compfoo);
CREATE TABLE
omm=# INSERT INTO t1_compfoo values(1,(1,'demo'));
INSERT 0 1
omm=# SELECT (b).f1 FROM t1_compfoo;
 f1 
----
  1

(1 row)

omm=# \d compfoo
Composite type "public.compfoo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
 f2     | text    | 
 
 omm=# select (b).f2 from t1_compfoo;
  f2  
------
 demo
(1 row)

omm=# 
omm=# select * from t1_compfoo;
 a |    b     
---+----------
 1 | (1,demo)
(1 row)

omm=# select b from t1_compfoo;
    b     
----------
 (1,demo)
(1 row)

–创建一个枚举类型

omm=# CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
CREATE TYPE

–查看类型

omm=#  select * from pg_enum;
enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16399 |             1 | create
     16399 |             2 | modify
     16399 |             3 | closed

2.修改类型定义
–重命名数据类型

omm=# ALTER TYPE compfoo RENAME TO compfoo1;
ALTER TYPE

–增加一个新的属性

omm=# ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
ALTER TYPE
omm=# \d compfoo1
Composite type "public.compfoo1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
 f2     | text    | 
 f3     | integer | 

omm=# select * from t1_compfoo;
 a |     b     
---+-----------
 1 | (1,demo,)
(1 row)

f3无值，为空，

–删除一个属性

omm=# ALTER TYPE compfoo1 drop ATTRIBUTE f1;
ALTER TYPE
omm=# \d compfoo1
omm=# Composite type "public.compfoo1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f2     | text    | 
 f3     | integer | 

select * from t1_compfoo;
 a |    b    
---+---------
 1 | (demo,)
(1 row)

–为枚举类型添加一个标签值

omm=# ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
ALTER TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16399 |             1 | create
     16399 |             2 | modify
     16399 |             3 | closed
     16399 |           2.5 | regress
(4 rows)

顺序号在closed的3前面

–重命名一个标签值

omm=# ALTER TYPE bugstatus RENAME VALUE 'create' TO 'new';
ALTER TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16399 |             2 | modify
     16399 |             3 | closed
     16399 |           2.5 | regress
     16399 |             1 | new
(4 rows)

第一个标签已修改，显示在最后
3.删除类型

omm=# DROP TYPE compfoo1;
ERROR:  cannot drop type compfoo1 because other objects depend on it
DETAIL:  table t1_compfoo column b depends on type compfoo1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

表引用了类型compfoo1，不能删除

omm=# DROP TYPE compfoo1 cascade;
NOTICE:  drop cascades to table t1_compfoo column b
DROP TYPE

omm=# \d+ t1_compfoo
                      Table "public.t1_compfoo"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 a      | integer |           | plain   |              | 
Has OIDs: no
Options: orientation=row, compression=no

连同类型相关的表的字段被删除

omm=# drop type bugstatus;
DROP TYPE

课程作业

1.创建一个复合类型，重命名复合类型，为复合类型增加属性、删除属性

omm=# create type comp1 as  (t1 int,t2 int,t3 char(10));
CREATE TYPE
omm=# alter type comp1 rename to comp2;
ALTER TYPE
omm=# alter type comp2 add ATTRIBUTE m1 int;
ALTER TYPE
omm=# alter type comp2 drop attribute t1;
ALTER TYPE
omm=# \d+ comp2
                Composite type "public.comp2"
 Column |     Type      | Modifiers | Storage  | Description 
--------+---------------+-----------+----------+-------------
 t2     | integer       |           | plain    | 
 t3     | character(10) |           | extended | 
 m1     | integer       |           | plain    |

2.创建一个枚举类型，新增标签值，重命名标签值

omm=# create type wine as enum('moutai','wuliangye','jingzhi');
CREATE TYPE
omm=# 
omm=# alter type wine  ADD VALUE IF NOT EXISTS 'shuijingfang';
ALTER TYPE
omm=# 
omm=# alter type wine rename value 'jingzhi' to 'erguotou';
ALTER TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder |  enumlabel   
-----------+---------------+--------------
     16410 |             1 | moutai
     16410 |             2 | wuliangye
     16410 |             4 | shuijingfang
     16410 |             3 | erguotou
(4 rows)

3.使用新创建的类型创建表

omm=# create table products(id int,subj wine);
CREATE TABLE
omm=# insert into products values (1,('moutai'));
INSERT 0 1
omm=# select * from products;
 id |  subj  
----+--------
  1 | moutai
(1 row)

4.删除类型

omm=# drop type comp2;
DROP TYPE
omm=# drop tupe wine;
ERROR:  syntax error at or near "tupe"
LINE 1: drop tupe wine;
             ^
omm=# drop type wine cascade;
NOTICE:  drop cascades to table products column subj
DROP TYPE
omm=# select * from products;
 id 
----
  1
(1 row)