修改列示例

• 修改列名

  -- 建表
  openGauss=# CREATE TABLE test_alt2(c1 INT,c2 INT);
  -- 修改列名
  openGauss=# ALTER TABLE test_alt2 RENAME c1 TO id;
  openGauss=# ALTER TABLE test_alt2 RENAME COLUMN c2 to areaid; 
  -- 查看
  openGauss=# \d test_alt1
     Table "public.test_alt1"
   Column |  Type   | Modifiers 
  --------+---------+-----------
   id     | integer | 
   areaid | integer | 

• 增加列

  -- 表test_alt1增加列
  openGauss=# ALTER TABLE IF EXISTS test_alt2 ADD COLUMN name VARCHAR(20);
  -- 查看
  openGauss=# \d test_alt2
             Table "public.test_alt1"
    Column  |         Type          | Modifiers 
  ----------+-----------------------+-----------
   id       | integer               | 
   areacode | integer               | 
   name     | character varying(20) |

• 修改列的数据类型

  -- 修改test_alt1表中name字段的类型
  openGauss=# ALTER TABLE test_alt1 MODIFY name VARCHAR(50);
  -- 查看
  openGauss=# \d test_alt1
            Table "public.test_alt2"
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   id     | integer               | 
   areaid | integer               | 
   name   | character varying(50) | 

  -- 修改test_alt1表中name字段的类型
  openGauss=# ALTER TABLE test_alt2 ALTER COLUMN name TYPE VARCHAR(25);
  -- 查看
  openGauss=# \d test_alt2
            Table "public.test_alt2"
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   id     | integer               | 
   areaid | integer               | 
   name   | character varying(25) | 

• 删除列

  -- 删除test_alt1中areaid字段
  openGauss=# ALTER TABLE test_alt2 DROP COLUMN areaid;
  -- 查看
  openGauss=# \d test_alt2
            Table "public.test_alt2"
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   id     | integer               | 
   name   | character varying(25) | 

• 修改字段存储模式

  --查看表详细信息。
  openGauss=# \d+ test_alt2
                                Table "public.test_alt2"
   Column |         Type          | Modifiers | Storage  | Stats target | Description 
  --------+-----------------------+-----------+----------+--------------+-------------
   id     | integer               |           | plain    |              | 
   name   | character varying(25) |           | extended |              | 
  Has OIDs: no
  Options: orientation=row, storage_type=USTORE
  
  --修改test_alt2表中name字段的存储模式。
  openGauss=# ALTER TABLE test_alt2 ALTER COLUMN name SET STORAGE PLAIN;
  
  --查看。
  openGauss=# \d+ test_alt2
                               Table "public.test_alt2"
   Column |         Type          | Modifiers | Storage | Stats target | Description 
  --------+-----------------------+-----------+---------+--------------+-------------
   id     | integer               |           | plain   |              | 
   name   | character varying(25) |           | plain   |              | 
  Has OIDs: no
  Options: orientation=row, storage_type=USTORE
  
  --删除。
  openGauss=# DROP TABLE test_alt2;