暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第12天 openGauss定义数据类型

openGauss每日一练第12天 openGauss定义数据类型

原创 Sally 2021-12-19
812

学习目标
学习openGauss定义数据类型

课程学习
连接数据库

#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r
1.创建类型
–创建一种复合类型

CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TABLE t1_compfoo(a int, b compfoo);
INSERT INTO t1_compfoo values(1,(1,‘demo’));
SELECT (b).f1 FROM t1_compfoo;
\d compfoo

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=# 
omm=# SELECT (b).f1 FROM t1_compfoo;
 f1 
----
  1
(1 row)

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

omm=# select * from t1_compfoo;
 1 | (1,demo)
(1 row)

omm=#  a |    b     
---+----------

–创建一个枚举类型

CREATE TYPE bugstatus AS ENUM (‘create’, ‘modify’, ‘closed’);

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

–查看类型

select * from pg_enum;

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

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

ALTER TYPE compfoo RENAME TO compfoo1;


omm=# ALTER TYPE compfoo RENAME TO compfoo1;
ALTER TYPE
omm=# \d compfoo
Did not find any relation named "compfoo".
omm=# \d compfoo1
Composite type "public.compfoo1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
 f2     | text    | 

omm=# \d t1_compfoo
   Table "public.t1_compfoo"
 Column |   Type   | Modifiers 
--------+----------+-----------
 a      | integer  | 
 b      | compfoo1 |

–增加一个新的属性

ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
\d compfoo1
select * from t1_compfoo;

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)

–删除一个属性

ALTER TYPE compfoo1 drop ATTRIBUTE f1;
\d compfoo1
select * from t1_compfoo;

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

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

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

ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS ‘regress’ BEFORE ‘closed’;

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)

–重命名一个标签值

ALTER TYPE bugstatus RENAME VALUE ‘create’ TO ‘new’;
select * from pg_enum;

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.删除类型
DROP TYPE compfoo1;
DROP TYPE compfoo1 cascade;
drop type bugstatus;

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.
omm=# DROP TYPE compfoo1 cascade;
NOTICE:  drop cascades to table t1_compfoo column b
DROP TYPE
omm=# select * from t1_compfoo;
 a 
---
 1
(1 row)

omm=# drop type bugstatus;
DROP TYPE

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

omm=# create type type1(id int,name char(10));
ERROR:  syntax error at or near "int"
LINE 1: create type type1(id int,name char(10));
                             ^
omm=# create type type1 as (id int,name char(10));
CREATE TYPE
omm=# create table table1(id int,name type1);
CREATE TABLE
omm=# insert into table1 values(1,(1,'aaa'));
INSERT 0 1
omm=# select * from table1;
 id |       name       
----+------------------
  1 | (1,"aaa       ")
(1 row)

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

omm=# create type type3 as enum('a','b','c');
CREATE TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16416 |             1 | a
     16416 |             2 | b
     16416 |             3 | c
(3 rows)

omm=# alter type type3 add vlue d; 
ERROR:  syntax error at or near "vlue"
LINE 1: alter type type3 add vlue d;
                             ^
omm=# alter type type3 add vlue 'd';
omm=# ERROR:  syntax error at or near "vlue"
LINE 1: alter type type3 add vlue 'd';
                             ^
alter type type3 add vlu 'd' 'd' 'd' 'd';'d' 'd' 'd' 'd';'d';
omm=# ERROR:  syntax error at or near "'d'"
LINE 1: alter type type3 'd';
                         ^

omm=# 
omm=# 
omm=# omm=# 

omm=# 
omm=# 
omm=# alter type type3 add vlues 'd';
ERROR:  syntax error at or near "vlues"
LINE 1: alter type type3 add vlues 'd';
                             ^
omm=# alter type type3 add value 'd';
ALTER TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16416 |             1 | a
     16416 |             2 | bomm=# 
     16416 |             3 | c
     16416 |             4 | d
(4 rows)

omm=# 

omm=# 
omm=# select * from pg_enum;
     16416 |             3 | c
     16416 |             4 | d
(4 rows)

omm=#  enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16416 |             1 | a
     16416 |             2 | b

omm=# 
omm=# 
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16416 |             1 | a
     16416 |             2 | b
     16416 |             3 | c
     16416 |             4 | d
(4 rows)

omm=# alter type type3 rename type4;
ERROR:  syntax error at or near "type4"
LINE 1: alter type type3 rename type4;
                                ^
omm=# alter type type3 rename to type4;
ALTER TYPE
omm=# select * from pg_enum;

     16416 |             4 | d
(4 rows)

omm=#  enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16416 |             1 | a
     16416 |             2 | b
     16416 |             3 | c

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

omm=# create table table1(id int,name type1);
CREATE TABLE
omm=# insert into table1 values(1,(1,'aaa'));
INSERT 0 1
omm=# select * from table1;
 id |       name       
----+------------------
  1 | (1,"aaa       ")
(1 row)

4.删除类型

omm=# drop type type2;
ERROR:  cannot drop type type2 because other objects depend on it
DETAIL:  table table1 column name depends on type type2
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# select * from table1;
 id |      name       
----+-----------------
  1 | ("aaa       ",)
(1 row)

omm=# drop table table1;
DROP TABLE
omm=# drop type type2;
DROP TYPE
omm=# drop type type4;
DROP TYPE
墨力计划opengauss
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论