openGauss | 数据类型

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

案例复制

创建一种复合类型
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
–创建一个枚举类型
CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
–查看类型
select * from pg_enum;

增加一个新的属性
ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
\d compfoo1
select * from t1_compfoo;

–删除一个属性
ALTER TYPE compfoo1 drop ATTRIBUTE f1;
\d compfoo1
select * from t1_compfoo;

–为枚举类型添加一个标签值
ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
–重命名一个标签值
ALTER TYPE bugstatus RENAME VALUE 'create' TO 'new';
select * from pg_enum;

作业内容

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

create type com1 as (a int, b text);
alter type com1 add attribute c text;
drop type com1 attribute b;	/*错误写法，直接drop*/
alter type com1 drop attribute b;
\d com1

omm=# create type com1 as (a int, b text);
CREATE TYPE
omm=# alter type com1 add attribute c text;
ALTER TYPE
omm=# alter type com1 drop attribute b;
ALTER TYPE
omm=# \d com1
 Composite type "public.com1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 c      | text    | 

omm=#

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

create type every1 as enum('geton','getoff','fly');
alter type every1 add value if not exists 'dive1' before 'fly';
alter type every1 rename value 'dive1' to 'dive';
select * from pg_enum;		/*不能用 \d every1 */

omm=# create type every1 as enum('geton','getoff','fly');
CREATE TYPE
omm=# alter type every1 add value if not exists 'dive1' before 'fly';
ALTER TYPE
omm=# alter type every1 rename value 'dive1' to 'dive';
ALTER TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16446 |             1 | geton
     16446 |             2 | getoff
     16446 |             3 | fly
     16446 |           2.5 | dive
(4 rows)

omm=# 

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

create table nice_tb (
	niceid int,
	nicename char(20),
	nicedetails com1,
	niceone every1
);

omm=# create table nice_tb (
omm(# niceid int,
omm(# nicename char(20),
omm(# nicedetails com1,
omm(# );
niceone every1
omm(# CREATE TABLE
omm=# 

4.删除类型

drop type every1;
drop type com1;
drop type com1 cascade;
drop type every1 cascade;

omm=# drop type every1;
ERROR:  cannot drop type every1 because other objects depend on it
DETAIL:  table nice_tb column niceone depends on type every1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# drop type com1;
ERROR:  cannot drop type com1 because other objects depend on it
DETAIL:  table nice_tb column nicedetails depends on type com1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# drop type com1 cascade;
NOTICE:  drop cascades to table nice_tb column nicedetails
DROP TYPE
omm=# drop type every1;
ERROR:  cannot drop type every1 because other objects depend on it
DETAIL:  table nice_tb column niceone depends on type every1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# drop type every1 cascade;
NOTICE:  drop cascades to table nice_tb column niceone
DROP TYPE