MogDB/PostgreSQL数据库SQL命令之ALTER TABLE

对于MogDB 2.1.0/PostgreSQL13.4数据库的ALTER TABLE命令可以实现哪些功能做了测试和整理。

首先创建测试表：

PostgreSQL13.4数据库：

create user test password 'test@123';
create tablespace tbs_01 LOCATION '/XX/XXX/tbs_01';
create table test (id int);
CREATE TABLE test1(id int, a int,  c serial,  sum_score int GENERATED ALWAYS AS (a+id) STORED );
CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
        NEW.created_date=now();
        RETURN NEW;
END
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER insert_trigger
BEFORE INSERT ON test
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func();
CREATE RULE notify_me AS ON UPDATE TO test DO ALSO NOTIFY test;
create table test2 (a int primary key, b varchar(10));
CREATE TYPE employee_type AS (name text, salary numeric);
create table test3 (name text,salary numeric);
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE tbs_01;
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

MogDB 2.1.0数据库：

create user test password 'test@123';
create tablespace tbs_01 LOCATION '/XX/XXX/tbs_01';
create table test (id int);
CREATE TABLE test1(id int, a int, c serial, sum_score int GENERATED ALWAYS AS (a+id) STORED );
CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
        NEW.created_date=now();
        RETURN NEW;
END
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER insert_trigger
BEFORE INSERT ON test
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func();
CREATE RULE "_RETURN" AS
ON SELECT TO test
DO INSTEAD
SELECT * FROM test where id =2;
create table test2 (a int primary key, b varchar(10));
CREATE TYPE employee_type AS (name text, salary numeric);
create table test3 (name text,salary numeric);
CREATE TABLE startend_pt (c1 INT, c2 INT) 
PARTITION BY RANGE (c2) (
    PARTITION p1 START(1) END(1000) EVERY(200) TABLESPACE tbs_01,
    PARTITION p2 END(2000),
    PARTITION p3 START(2000) END(2500),
    PARTITION p4 START(2500),
    PARTITION p5 START(3000) END(5000) EVERY(1000))
ENABLE ROW MOVEMENT;
CREATE TABLE test5 (c1 INT, c2 INT);
insert into test5 values(1,1015);
CREATE INDEX idx_startend_pt_c2 ON startend_pt(c2) LOCAL; 

ALTER TABLE 功能比对结果如下：