概述

本文档记录openGauss 3.0.0数据库每日一练第16天课程作业，学习openGuass数据库中如何对表进行修改。

课程练习

创建表，为表添加字段

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day16 RELATIVE LOCATION 'tablespace/day16';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb16 WITH TABLESPACE = day16;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER user16 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#ALTER USER user16  SYSADMIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb16 user16
Password for user user16: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb16" as user "user16".
user16@local:/opt/huawei/tmp [musicdb16]=>create table tab_day16(
musicdb16(>       id bigint,
musicdb16(>       name varchar(50) not null,
musicdb16(>       age  int default 20,
musicdb16(>       primary key(id)
musicdb16(>      );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tab_day16_pkey" for table "tab_day16"
CREATE TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d
                           List of relations
 Schema |   Name    | Type  | Owner  |             Storage              
--------+-----------+-------+--------+----------------------------------
 public | tab_day16 | table | user16 | {orientation=row,compression=no}
(1 row)
user16@local:/opt/huawei/tmp [musicdb16]=>alter table tab_day16 add column addr varchar(100);
ALTER TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16
           Table "public.tab_day16"
 Column |          Type          | Modifiers  
--------+------------------------+------------
 id     | bigint                 | not null
 name   | character varying(50)  | not null
 age    | integer                | default 20
 addr   | character varying(100) |           <=================
Indexes:
    "tab_day16_pkey" PRIMARY KEY, btree (id) TABLESPACE day16

删除表中的已有字段

user16@local:/opt/huawei/tmp [musicdb16]=>alter table tab_day16 drop column addr;
ALTER TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16
          Table "public.tab_day16"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | default 20
Indexes:
    "tab_day16_pkey" PRIMARY KEY, btree (id) TABLESPACE day16

删除表的已有约束、添加约束

--删除约束
user16@local:/opt/huawei/tmp [musicdb16]=>alter table tab_day16 drop constraint tab_day16_pkey;
ALTER TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16
          Table "public.tab_day16"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | default 20

user16@local:/opt/huawei/tmp [musicdb16]=>select * from pg_constraint  where conname like 'tab_day16_pkey';
 conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | confrelid | 
confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | consoft | conopt | conkey | confkey | co
npfeqop | conppeqop | conffeqop | conexclop | conbin | consrc | conincluding 
---------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-----------+-
------------+-------------+---------------+------------+-------------+--------------+---------+--------+--------+---------+---
--------+-----------+-----------+-----------+--------+--------+--------------
(0 rows)


--添加约束
user16@local:/opt/huawei/tmp [musicdb16]=>alter table tab_day16 add constraint test_pkey primary key(id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_pkey" for table "tab_day16"
ALTER TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16
          Table "public.tab_day16"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | default 20
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE day16

user16@local:/opt/huawei/tmp [musicdb16]=>\x
Expanded display is on.
user16@local:/opt/huawei/tmp [musicdb16]=>select * from pg_constraint  where conname like 'test_pkey';
-[ RECORD 1 ]-+----------
conname       | test_pkey
connamespace  | 2200
contype       | p
condeferrable | f
condeferred   | f
convalidated  | t
conrelid      | 24720
contypid      | 0
conindid      | 24726
confrelid     | 0
confupdtype   |  
confdeltype   |  
confmatchtype |  
conislocal    | t
coninhcount   | 0
connoinherit  | t
consoft       | f
conopt        | f
conkey        | {1}
confkey       | 
conpfeqop     | 
conppeqop     | 
conffeqop     | 
conexclop     | 
conbin        | 
consrc        | 
conincluding  | 

修改表字段的默认值

user16@local:/opt/huawei/tmp [musicdb16]=>alter table tab_day16 alter column age set default 25;
ALTER TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16
          Table "public.tab_day16"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | default 25  <===============
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE day16

修改表字段的数据类型

user16@local:/opt/huawei/tmp [musicdb16]=>alter table tab_day16 ALTER COLUMN age TYPE bigint;
ALTER TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16
          Table "public.tab_day16"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | bigint                | default 25  <========
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE day16

修改表字段的名字

user16@local:/opt/huawei/tmp [musicdb16]=>ALTER TABLE tab_day16 RENAME COLUMN age TO stuage;
ALTER TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16
          Table "public.tab_day16"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 stuage | bigint                | default 25  <======
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE day16

修改表的名字

user16@local:/opt/huawei/tmp [musicdb16]=>ALTER TABLE tab_day16 RENAME TO tab_day16bak;
ALTER TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16
Did not find any relation named "tab_day16".
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16bak
         Table "public.tab_day16bak"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 stuage | bigint                | default 25
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE day16

删除表

user16@local:/opt/huawei/tmp [musicdb16]=>drop table tab_day16bak;
DROP TABLE
user16@local:/opt/huawei/tmp [musicdb16]=>\d tab_day16bak
Did not find any relation named "tab_day16bak".