3.2.5.1. PostgreSQL

3.2.5.1.1. 清空表

语法

TRUNCATE TABLE TABLE_NAME

示例

    CREATE TABLE unisql_truncate_test(id int, name varchar(10));

INSERT INTO unisql_truncate_test values(1,'aa');
INSERT INTO unisql_truncate_test values(2,'bb');
INSERT INTO unisql_truncate_test values(3,'cc');

-- 转换前Oracle SQL:
TRUNCATE TABLE unisql_truncate_test;

-- 转换后PostgreSQL SQL:
TRUNCATE TABLE unisql_truncate_test

-- 查询表数据
SELECT * FROM unisql_truncate_test;

3.2.5.1.2. 创建索引

语法

CREATE [UNIQUE|BITMAP] INDEX [SCHEMA.] INDEX_NAME
ON TABLE_NAME ( COLUMN1, COLUMN2, ..., COLUMNN )
[{ LOGGING |  NOLOGGING}]
[{ GLOBAL  |  LOCAL}]
[{ USABLE  |  UNUSABLE}]
[{ IMMEDIATE INVALIDATION  |  UNUSABLE}]
[tablespace tablespaceName] [local]

示例

--创建表
CREATE TABLE unisql_index_test(col1 int,col2 int,col3 int,col4 int,col5 varchar(10))

-- 删除存在的索引
DROP INDEX unisql_index_test_idx;

-- 转换前Oracle SQL:
-- 使用示例
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE UNIQUE INDEX unisql_index_test_idx ON unisql_index_test(col1);
CREATE BITMAP INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX test.unisql_index_test_idx ON unisql_index_test(col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1 asc);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1 desc);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1,col2);
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) LOGGING;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) NOLOGGING;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) GLOBAL;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) USABLE;
CREATE INDEX unisql_index_test_idx ON unisql_index_test(col1) UNUSABLE;

-- 转换后PostgreSQL SQL:
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE UNIQUE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1 DESC);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1, col2);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);
CREATE INDEX unisql_index_test_idx ON unisql_index_test (col1);


-- 创建表，LOCAL索引需要创建在分区表上
CREATE TABLE unisql_local_test_orders (
    order_id   int,
    order_num int,
    customer   varchar(50)
)
PARTITION BY RANGE (order_num)
(
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (200),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

-- 删除存在的索引
DROP INDEX unisql_local_test_orders_idx;

-- 转换前Oracle SQL:
-- 使用示例
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders(order_num) LOCAL;
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders(order_num) tablespace test LOCAL;

-- 转换后PostgreSQL SQL:
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders (order_num);
CREATE INDEX unisql_local_test_orders_idx ON unisql_local_test_orders (order_num) TABLESPACE test;

3.2.5.1.3. 删除索引

语法

DROP INDEX INDEX_NAME

示例

-- 转换前Oracle SQL:
DROP INDEX unisql_index_test_idx;

-- 转换后PostgreSQL SQL:
DROP INDEX unisql_index_test_idx;

3.2.5.1.4. 复制表

语法

CREATE [GLOBAL TEMPORARY] TABLE TABLE_NAME AS (select_list)

示例

CREATE TABLE unisql_copy_table_test(id int, name varchar(10));
INSERT INTO unisql_copy_table_test values(1,'TOM');
INSERT INTO unisql_copy_table_test values(2,'JIMMY');
INSERT INTO unisql_copy_table_test values(3,'NANCY');

-- 转换前Oracle SQL:
CREATE TABLE unisql_copy_table_test_new AS SELECT id,name FROM unisql_copy_table_test WHERE id > 1;
CREATE GLOBAL TEMPORARY TABLE unisql_copy_table_test_global AS SELECT id,name FROM unisql_copy_table_test WHERE id > 1;
CREATE GLOBAL TEMPORARY TABLE unisql_copy_table_test_global_1 AS SELECT * FROM duaL;

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_copy_table_test_new AS SELECT id,name FROM unisql_copy_table_test WHERE id>1;
CREATE TEMPORARY TABLE unisql_copy_table_test_global AS SELECT id,name FROM unisql_copy_table_test WHERE id>1;
CREATE TEMPORARY TABLE unisql_copy_table_test_global_1 AS SELECT 'X' AS DUMMY;

3.2.5.1.5. 创建表,包含临时表等

语法

CREATE [GLOBAL TEMPORARY] TABLE TABLE_NAME (COLUMN1 DATATYPE [NOT NULL | NULL] [DEFAULT] [PRIMARY KEY],[table_constraint]...)
[ ON COMMIT { DELETE | PRESERVE } ROWS ]

table_constraint:
[ CONSTRAINT constraint_name ]
{UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters}

DEFAULT 后面表达式可支持使用部分函数、关键字函数、字符串、常数、序列、字符串连接符。

示例

-- 转换前Oracle SQL:
CREATE table unisql_create_table_test(
    DBID_   NUMERIC(19)      NOT NULL,
    PROCESS_KEY     VARCHAR2(128)    NOT NULL,
    BIZ_VERSION     VARCHAR2(32)     NOT NULL,
    PROCESS_NAME    VARCHAR2(128)    NOT NULL,
    PROCESS_FLAG    VARCHAR2(1)         NOT NULL,
    PROCESS_CLASS   VARCHAR2(128)    NOT NULL,
    IGNORE_EXIST_USER       VARCHAR2(1)     default 0 NOT NULL,
    FORM_NAME       VARCHAR2(128),
    FORM_TYPE       VARCHAR2(128),
    FORM_URL        VARCHAR2(128),
    FORM_UUID       VARCHAR2(32),
    IMPORT_TIME     TIMESTAMP       default CURRENT_TIMESTAMP,
    HANDLE_TIME     TIMESTAMP,
    STATE_      VARCHAR2(20)        default 0,
    ERRORMSG        CLOB,
    constraint UK_IMPORT_PRO UNIQUE (PROCESS_KEY,BIZ_VERSION,PROCESS_NAME),
    constraint UK_PROCESS_BIZ UNIQUE (PROCESS_KEY,BIZ_VERSION),
    constraint UK_IMPORT_PRO_NAME UNIQUE (PROCESS_NAME,BIZ_VERSION),
    constraint PK_IMPORT_PRO primary key (DBID_)
);

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_create_table_test (DBID_ decimal(19) NOT NULL,PROCESS_KEY varchar(128) NOT NULL,BIZ_VERSION varchar(32) NOT NULL,PROCESS_NAME varchar(128) NOT NULL,PROCESS_FLAG varchar(1) NOT NULL,PROCESS_CLASS varchar(128) NOT NULL,IGNORE_EXIST_USER varchar(1) DEFAULT 0 NOT NULL,FORM_NAME varchar(128),FORM_TYPE varchar(128),FORM_URL varchar(128),FORM_UUID varchar(32),IMPORT_TIME timestamp DEFAULT CURRENT_TIMESTAMP(0),HANDLE_TIME timestamp,STATE_ varchar(20) DEFAULT 0,ERRORMSG text,CONSTRAINT UK_IMPORT_PRO UNIQUE(PROCESS_KEY, BIZ_VERSION, PROCESS_NAME),CONSTRAINT UK_PROCESS_BIZ UNIQUE(PROCESS_KEY, BIZ_VERSION),CONSTRAINT UK_IMPORT_PRO_NAME UNIQUE(PROCESS_NAME, BIZ_VERSION),CONSTRAINT PK_IMPORT_PRO PRIMARY KEY(DBID_))


-- 转换前Oracle SQL:
CREATE GLOBAL TEMPORARY TABLE unisql_create_table_on_option_test(
    d_SMALLINT SMALLINT ,
    d_INTEGER INTEGER,
    d_INT INT ,
    d_LONG LONG ,
    d_NUMBER NUMBER(10,2),
    d_DECIMAL DECIMAL(10,2),
    d_CHAR CHAR(60),
    d_CHARACTER CHARACTER(60) ,
    d_VARCHAR VARCHAR(60),
    d_VARCHAR2 VARCHAR2(60),
    d_DATE DATE,
    d_TIMESTAMP TIMESTAMP(6),
    d_TIMESTAMP2 TIMESTAMP(6) WITH TIME ZONE,
    d_BLOB BLOB,
    d_CLOB CLOB,
    d_XMLTYPE XMLTYPE
    ) ON COMMIT PRESERVE ROWS;

-- 转换后PostgreSQL SQL:
CREATE TEMPORARY TABLE unisql_create_table_on_option_test (d_SMALLINT bigint,d_INTEGER bigint,d_INT bigint,d_LONG text,d_NUMBER decimal(10,2),d_DECIMAL decimal(10,2),d_CHAR char(60),d_CHARACTER char(60),d_VARCHAR varchar(60),d_VARCHAR2 varchar(60),d_DATE timestamp(0),d_TIMESTAMP timestamp(6),d_TIMESTAMP2 timestamp(6) with time zone,d_BLOB bytea,d_CLOB text,d_XMLTYPE xml)


CREATE SEQUENCE unisql_default_expr_seq

-- 转换前Oracle SQL:
CREATE TABLE unisql_default_expr_table (
    col_not_null VARCHAR2 ( 1 ) DEFAULT 1 NOT NULL,
    col_current_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    col_current_localstamp DATE DEFAULT LOCALTIMESTAMP,
    col_sysdate DATE DEFAULT SYSDATE,
    col_systimestamp DATE DEFAULT SYSTIMESTAMP,
    col_name VARCHAR2(50) DEFAULT 'John Doe',
    col_to_date DATE DEFAULT TO_DATE('2000-01-01', 'YYYY-MM-DD'),
    col_nextval NUMBER DEFAULT unisql_default_expr_seq.NEXTVAL,
    col_currval NUMBER DEFAULT unisql_default_expr_seq.CURRVAL
)

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_default_expr_table (
    col_not_null VARCHAR ( 1 ) DEFAULT 1 NOT NULL,
    col_current_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ( 0 ),
    col_current_localstamp TIMESTAMP ( 0 ) DEFAULT CURRENT_TIMESTAMP ( 0 ),
    col_sysdate TIMESTAMP ( 0 ) DEFAULT statement_timestamp (),
    col_systimestamp TIMESTAMP ( 0 ) DEFAULT statement_timestamp (),
    col_name VARCHAR ( 50 ) DEFAULT 'John Doe',
    col_to_date TIMESTAMP ( 0 ) DEFAULT CAST( to_timestamp ( '2000-01-01', 'YYYY-MM-DD' ) AS TIMESTAMP ),
    col_nextval DECIMAL DEFAULT nextval ( 'unisql_default_expr_seq' ),
    col_currval DECIMAL DEFAULT currval ( 'unisql_default_expr_seq' )
)

-- 转换前Oracle SQL:
create table PARTITION (
    id int,
    name varchar2(100),
    name_desc varchar2(100),
    trans_name varchar2(100),
    test_values varchar2(100),
    values1 varchar2(100),
    PARTITION int
);

-- 转换后PostgreSQL SQL:
CREATE TABLE PARTITION (id bigint,name varchar2(100),name_desc varchar2(100),trans_name varchar2(100),test_values varchar2(100),values1 varchar2(100),PARTITION bigint);

3.2.5.1.6. 创建分区表

分区键仅支持列，分区范围支持使用函数、字符串、常数表达，统一SQL会根据表名、分区名在转化库中创建分区表，分区表名为：表名_1_prt_分区名， Oracle 源语句HASH分区未指定分区名和分区数目时，转化到PostgreSQL表分区名默认为p0，指定多个分区数目，转化后的分区名依次递增（p0,p1,p2,…）。Oracle 和 PostgreSQL HASH分区算法不同，所以插入的数据分布到分区表表现也不一致。 分区表的唯一、主键的表级约束，必须与分区字段一致（或包含分区字段），才能保证全局的唯一性，否则无法实现转化，唯一、主键的列级约束的这个限制暂不考虑。

语法

CREATE TABLE TABLE_NAME …PARTITION BY[RANGE|LIST] (column) (partition_list);

CREATE TABLE TABLE_NAME …PARTITION BY HASH (column[,column]) [PARTITIONS number];

示例

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_range_test
(
product_id   NUMBER(6),
sale_time    TIMESTAMP(6),
sale_price   NUMBER(10,2)
)
PARTITION BY RANGE (sale_time)
(
PARTITION q1_2023 VALUES LESS THAN (TO_DATE('2023/04/01', 'yyyy/mm/dd')),
PARTITION q2_2023 VALUES LESS THAN (TO_DATE('2023/07/01', 'yyyy/mm/dd')),
PARTITION q3_2023 VALUES LESS THAN (TO_DATE('2023/10/01', 'yyyy/mm/dd')),
PARTITION q4_2023 VALUES LESS THAN (TO_DATE('2024/01/01', 'yyyy/mm/dd'))
);

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_range_test (product_id int,sale_time timestamp(6),sale_price decimal(10,2)) PARTITION BY RANGE (sale_time);
CREATE TABLE unisql_partition_by_range_test_1_prt_q1_2023 PARTITION OF unisql_partition_by_range_test for values from  (MINVALUE)  to (CAST(to_timestamp('2023/04/01', 'yyyy/mm/dd') AS timestamp));
CREATE TABLE unisql_partition_by_range_test_1_prt_q2_2023 PARTITION OF unisql_partition_by_range_test for values from (CAST(to_timestamp('2023/04/01', 'yyyy/mm/dd') AS timestamp)) to (CAST(to_timestamp('2023/07/01', 'yyyy/mm/dd') AS timestamp));
CREATE TABLE unisql_partition_by_range_test_1_prt_q3_2023 PARTITION OF unisql_partition_by_range_test for values from (CAST(to_timestamp('2023/07/01', 'yyyy/mm/dd') AS timestamp)) to (CAST(to_timestamp('2023/10/01', 'yyyy/mm/dd') AS timestamp));
CREATE TABLE unisql_partition_by_range_test_1_prt_q4_2023 PARTITION OF unisql_partition_by_range_test for values from (CAST(to_timestamp('2023/10/01', 'yyyy/mm/dd') AS timestamp)) to (CAST(to_timestamp('2024/01/01', 'yyyy/mm/dd') AS timestamp));

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition (
unisql_id NUMBER,
unisql_name VARCHAR2(50),
unisql_salary NUMBER(10,2),
unisql_hire_date DATE,
CONSTRAINT unisql_partition_pk PRIMARY KEY (unisql_id, unisql_hire_date)
)PARTITION BY RANGE (unisql_hire_date) (
PARTITION unisql_partition_p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p3 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p4 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition (unisql_id decimal,unisql_name varchar(50),unisql_salary decimal(10,2),unisql_hire_date timestamp(0),CONSTRAINT unisql_partition_pk PRIMARY KEY(unisql_id, unisql_hire_date)) PARTITION BY RANGE (unisql_hire_date);
CREATE TABLE unisql_partition_1_prt_unisql_partition_p1 PARTITION OF unisql_partition for values from  (MINVALUE)  to (CAST(to_timestamp('2023-01-01', 'YYYY-MM-DD') AS timestamp));
CREATE TABLE unisql_partition_1_prt_unisql_partition_p2 PARTITION OF unisql_partition for values from (CAST(to_timestamp('2023-01-01', 'YYYY-MM-DD') AS timestamp)) to (CAST(to_timestamp('2024-01-01', 'YYYY-MM-DD') AS timestamp));
CREATE TABLE unisql_partition_1_prt_unisql_partition_p3 PARTITION OF unisql_partition for values from (CAST(to_timestamp('2024-01-01', 'YYYY-MM-DD') AS timestamp)) to (CAST(to_timestamp('2025-01-01', 'YYYY-MM-DD') AS timestamp));
CREATE TABLE unisql_partition_1_prt_unisql_partition_p4 PARTITION OF unisql_partition for values from (CAST(to_timestamp('2025-01-01', 'YYYY-MM-DD') AS timestamp)) to (CAST(to_timestamp('2026-01-01', 'YYYY-MM-DD') AS timestamp));


-- 转换前Oracle SQL:
create table unisql_partition_by_list_test
(
id varchar2(15) not null,
city varchar2(20)
)
partition by list (city)
(
partition p1 values ('beijing'),
partition p2 values ('shanghai'),
partition p3 values ('changsha'),
partition p4 values (default)
);

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_list_test (id varchar(15) NOT NULL,city varchar(20)) PARTITION BY LIST (city);
CREATE TABLE unisql_partition_by_list_test_1_prt_p1 PARTITION OF unisql_partition_by_list_test for values in ('beijing');
CREATE TABLE unisql_partition_by_list_test_1_prt_p2 PARTITION OF unisql_partition_by_list_test for values in ('shanghai');
CREATE TABLE unisql_partition_by_list_test_1_prt_p3 PARTITION OF unisql_partition_by_list_test for values in ('changsha');
CREATE TABLE unisql_partition_by_list_test_1_prt_p4 PARTITION OF unisql_partition_by_list_test DEFAULT;

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 INT, col2 VARCHAR(50), col3 TIMESTAMP )
PARTITION BY HASH (col1);

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp) PARTITION BY HASH (col1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 1, REMAINDER 0);

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 int,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE (col1, col2))
PARTITION BY HASH (col1,col2) PARTITIONS 4;

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE(col1, col2)) PARTITION BY HASH (col1,col2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p1 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p2 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p3 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 转换前Oracle SQL:
create table unisql_hash_part(
id        number,
pro_num   varchar2(40),
app_type  varchar2(40)
)
partition by hash(pro_num)
(
partition part_01,
partition part_02,
partition part_03)

-- 转换后PostgreSQL SQL:
不支持

3.2.5.1.7. 清空、删除分区表

语法

ALTER TABLE TABLE_NAME [TRUNCATE|DROP] PARTITION PARTITION_NAMES

示例

-- 转换前Oracle SQL:
ALTER TABLE unisql_partition_by_range_test TRUNCATE  PARTITION q1_2023;
ALTER TABLE unisql_partition_by_range_test DROP  PARTITION q1_2023;

-- 转换后PostgreSQL SQL:
TRUNCATE TABLE unisql_partition_by_range_test_1_prt_q1_2023
DROP TABLE unisql_partition_by_range_test_1_prt_q1_2023

3.2.5.1.8. 新增列

语法

ALTER TABLE ADD COLUMN_NAME {column_definition | (column_definition_list)}


column_definition_list:
column_definition [, column_definition ...]

column_definition:
column_name data_type
[DEFAULT const_value] [AUTO_INCREMENT]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment

示例

-- 创建表
CREATE TABLE unisql_add_column_test(col1 int);

-- 转换前Oracle SQL:
ALTER TABLE unisql_add_column_test ADD col2 NUMBER(4);
ALTER TABLE unisql_add_column_test ADD col3 varchar(10) NOT NULL ;
ALTER TABLE unisql_add_column_test ADD col4 NUMBER(4) DEFAULT 0;
ALTER TABLE unisql_add_column_test ADD (col5 NUMBER(4), col6 INT);

-- 转换后PostgreSQL SQL:
ALTER TABLE unisql_add_column_test ADD COLUMN col2 smallint
ALTER TABLE unisql_add_column_test ADD COLUMN col3 varchar(10) NOT NULL
ALTER TABLE unisql_add_column_test ADD COLUMN col4 smallint DEFAULT 0
ALTER TABLE unisql_add_column_test ADD COLUMN col5 smallint, ADD COLUMN col6 bigint

3.2.5.1.9. 新增表约束

语法

ALTER TABLE table_name alter_table_action_list;

alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action:
    ADD [CONSTRAINT [constraint_name]] UNIQUE (column_name [, column_name ]...)
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name_list) references_clause
    | ADD [CONSTRAINT [constraint_name]] CHECK (expr)
    | ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)
    | ADD CONSTRAINT constraint_name FOREIGN KEY(foreign_col_name) REFERENCES
        reference_tbl_name(column_name)
    constraint_option

constraint_option:
    [disable | enable]

示例

-- 注意下面的示例中，因为约束名都相同，所以在测试时，注意在执行时出现冲突错误，请先删除存在的约束。再进行后续测试

-- 创建表
CREATE TABLE unisql_cn_test(a int,b int,c int,d varchar2(10),e varchar2(10));

-- 删除约束
ALTER TABLE unisql_cn_test DROP CONSTRAINT cn_name_a;

-- 测试主键或者唯一约束
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a,b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE (a,b);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a) disable;
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY (a) enable;

-- 转换后PostgreSQL SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY(a)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY(a, b)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE(a)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE(a, b)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY(a)
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a PRIMARY KEY(a)


-- 测试reference约束
-- 创建一张关联表
CREATE TABLE unisql_cn_ref_tab(col1 int,col2 int,col3 int,col4 varchar2(10),col5 varchar2(10));


-- 删除约束
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1;
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1_col2;

-- 添加一个外键约束(指定一个列)
ALTER TABLE unisql_cn_ref_tab ADD CONSTRAINT ref_tab_col1 PRIMARY KEY (col1);

-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a) REFERENCES unisql_cn_ref_tab(col1);

-- 转换后PostgreSQL SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a) REFERENCES unisql_cn_ref_tab(col1)


-- 添加一个外键约束(指定两个列)
ALTER TABLE unisql_cn_ref_tab ADD CONSTRAINT ref_tab_col1_col2 PRIMARY KEY (col1,col2);

-- 添加两个列约束
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a,b) REFERENCES unisql_cn_ref_tab(col1,col2);

-- 转换后PostgreSQL SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a FOREIGN KEY (a, b) REFERENCES unisql_cn_ref_tab(col1, col2)

3.2.5.1.10. 删除表约束

语法

ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME

示例

-- 转换前Oracle SQL:
    -- 删除约束
ALTER TABLE unisql_cn_test DROP CONSTRAINT cn_name_a;
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1;
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1_col2;

-- 转换后PostgreSQL SQL:
ALTER TABLE unisql_cn_test DROP CONSTRAINT cn_name_a
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1
ALTER TABLE unisql_cn_ref_tab DROP CONSTRAINT ref_tab_col1_col2

3.2.5.1.11. 修改列的属性

警告

PostgreSQL内核暂不支持使用ALTER MODIFY

3.2.5.1.12. 修改表名

语法

RENAME TABLE_NAME TO NEW_TABLE_NAME;

ALTER TABLE TABLE_NAME RENAME TO NEW_TABLE_NAME;

示例

CREATE TABLE unisql_rename_table_test(id int,name varchar(10));

-- 转换前Oracle SQL:
RENAME unisql_rename_table_test TO unisql_rename_table_test_new;
ALTER TABLE unisql_rename_table_test_new RENAME TO unisql_rename_table_test;

-- 转换后PostgreSQL SQL:
ALTER TABLE unisql_rename_table_test RENAME TO unisql_rename_table_test_new
ALTER TABLE unisql_rename_table_test_new RENAME TO unisql_rename_table_test

3.2.5.1.13. 注释表名,列名

语法

COMMENT ON TABLE {object_name|COLUMN relation_name.column_name} IS string_literal;

示例

CREATE TABLE unisql_comment_table_test(id int,name varchar(10));

-- 转换前Oracle SQL:
COMMENT ON COLUMN unisql_comment_table_test.id IS 'unisql_comment_table_test id column';
COMMENT ON TABLE unisql_comment_table_test IS 'unisql_comment_table_test table';

-- 转换后PostgreSQL SQL:
COMMENT ON COLUMN unisql_comment_table_test.id IS 'unisql_comment_table_test id column'
COMMENT ON TABLE unisql_comment_table_test IS 'unisql_comment_table_test table'

3.2.5.1.14. 修改表

支持普通表修改为分区表的转换。参考示例如下：

语法

PostgreSQL不支持

3.2.5.1.15. 创建视图

语法

CREATE [OR REPLACE] [[NO] FORCE] VIEW view_name [(column_name_list)] AS select_stmt;

 column_name_list:
     column_name [, column_name ...]

描述

该语句用于创建视图语句，如果指定了 OR REPLACE 子句，该语句能够替换已有的视图

示例

-- 创建表
CREATE TABLE unisql_view_test(a int,b int,c varchar(10));

-- 创建视图示例
-- 转换前Oracle SQL:
CREATE VIEW unisql_view_1 AS SELECT * FROM unisql_view_test;
CREATE VIEW unisql_view_2 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW public.unisql_view_3 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW unisql_view_4 AS SELECT a,b FROM unisql_view_test;
CREATE VIEW unisql_view_5 AS SELECT ' ' a, ' ' b FROM dual;
CREATE VIEW unisql_view_6 AS SELECT 1 a, 2 b FROM dual;
CREATE OR REPLACE VIEW unisql_view_7 AS SELECT 1 a, 2 b FROM dual;
CREATE OR REPLACE VIEW unisql_view_8 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE FORCE VIEW unisql_view_9 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE NOFORCE VIEW unisql_view_10 AS SELECT 1 a, 2 b, '3' c FROM dual;
CREATE VIEW unisql_view_11(a1,b1) AS SELECT a,b FROM unisql_view_test;

-- 转换后PostgreSQL SQL:
CREATE VIEW unisql_view_1 AS SELECT * FROM unisql_view_test
CREATE VIEW unisql_view_2 AS SELECT a,b FROM unisql_view_test
CREATE VIEW public.unisql_view_3 AS SELECT a,b FROM unisql_view_test
CREATE VIEW unisql_view_4 AS SELECT a,b FROM unisql_view_test
CREATE VIEW unisql_view_5 AS SELECT ' ' AS a,' ' AS b
CREATE VIEW unisql_view_6 AS SELECT 1 AS a,2 AS b
CREATE OR REPLACE VIEW unisql_view_7 AS SELECT 1 AS a,2 AS b
CREATE OR REPLACE VIEW unisql_view_8 AS SELECT 1 AS a,2 AS b,'3' AS c
CREATE VIEW unisql_view_9 AS SELECT 1 AS a,2 AS b,'3' AS c
CREATE VIEW unisql_view_10 AS SELECT 1 AS a,2 AS b,'3' AS c
CREATE VIEW unisql_view_11 (a1,b1) AS SELECT a,b FROM unisql_view_test

3.2.5.1.16. 创建序列

语法

CREATE SEQUENCE [ schema. ] sequence_name
 { START WITH int_value
 |[ INCREMENT BY int_value ]
 |[ MINVALUE int_value | NOMINVALUE ]
 |[ MAXVALUE int_value | NOMAXVALUE ]
 |[ CACHE int_value | NOCACHE ]
 |[ ORDER | NOORDER ]
 |[ CYCLE | NOCYCLE ]
 }
 ;

描述

该语句用来创建序列。序列属于数据库对象，用户可以从序列中生成唯一整数。

参数解释

字段	描述
schema.	指定包含序列的 Schema。如果省略 Schema，则数据库会在用户当前所在的 Schema 中创建序列。
sequence_name	要创建的序列名称。
MINVALUE int_value	指定序列的最小值，int_value 的取值范围是 [-9223372036854775808，9223372036854775807]。
NOMINVALUE	NOMINVALUE 为默认值。 如果指定为 NOMINVALUE，则升序时最小值为 1，降序时最小值为 -9223372036854775808。
MAXVALUE int_value	指定序列的最大值，int_value 的取值范围是 [-9223372036854775808，9223372036854775807]。注意:同时指定 MINVALUE 和 MAXVALUE 时，MINVALUE 必须小于 MAXVALUE。MAXVALUE 和 MINVALUE 之差必须大于等于 INCREMENT BY 中 的 int_value。
NOMAXVALUE	NOMAXVALUE 为默认值。 如果指定为 NOMAXVALUE，则升序时最大值为9223372036854775807，降序时最大值为 -1。
START WITH int_value	指定序列的起始值。int_value 必须小于等于 MAXVALUE，且大于等于 MINVALUE。 如果不指定，则升序时默认取值为最小值，降序时默认取值为最大值。
INCREMENT BY int_value	指定序列的自增步数。int_value 不可以为 0。 如果指定为正数，则序列为升序；如果指定为负数，则序列为降序。 不指定时缺省值为 1。
CACHE int_value	指定在内存中预分配的自增值个数，int_value 默认为 20。CACHE int_value 的值必须大于 1，如果 CACHE INT_VALUE 的值为 1，则等价于 NOCACHE。
NOCACHE	指定序列的值为未预先分配。 如果同时省略 CACHE 和 NOCACHE，则数据库默认缓存 20 个序列号。
ORDER	指定序列的值按顺序生成。
NOORDER	NOORDER 为默认值，即不保证序列的值按顺序生成。
CYCLE	指定序列的值循环生成，即序列在达到其最大值或最小值后继续生成值。在升序达到最大值后，它会生成最小值。在降序序列达到其最小值后，它会生成其最大值。注意: 如果指定了 CYCLE，而且 INCREMENT BY int_value 小于 0 时，必须指定 MINVALUE。如果指定了 CYCLE，CACHE 的数量不能超过一个 CYCLE。
NOCYCLE	NOCYCLE 为默认值，指定序列在达到其最大值或最小值后无法生成更多值。

示例

-- 创建序列示例
-- 转换前Oracle SQL:
CREATE SEQUENCE SE_NAME MAXVALUE 100000 CYCLE;
CREATE SEQUENCE SE_NAME_1 NOCYCLE;
CREATE SEQUENCE SE_NAME_2 CACHE 20;
CREATE SEQUENCE SE_NAME_3 START WITH 1 NOCACHE;
CREATE SEQUENCE SE_NAME_4 START WITH 1 ORDER;
CREATE SEQUENCE SE_NAME_5 START WITH 1 NOORDER;
CREATE SEQUENCE SE_NAME_6 START WITH 1 KEEP;
CREATE SEQUENCE SE_NAME_7 NOKEEP START WITH 1;
CREATE SEQUENCE SE_NAME_8 NOMINVALUE MAXVALUE 9223372036854775808;
CREATE SEQUENCE SE_NAME_9 MAXVALUE 9223372036854775806;
CREATE SEQUENCE SE_NAME_10 MINVALUE -9223372036854775808 NOMAXVALUE;
CREATE SEQUENCE SE_NAME_11 MINVALUE -9223372036854775809;
CREATE SEQUENCE SE_NAME_12 MINVALUE -9999999999999999999999999999;
CREATE SEQUENCE SE_NAME_13 INCREMENT BY 9223372036854775808;
CREATE SEQUENCE SE_NAME_14 INCREMENT BY -9223372036854775809;
CREATE SEQUENCE SE_NAME_15 MINVALUE -9999999999999999999999999999;
CREATE SEQUENCE SE_NAME_16 INCREMENT BY 9223372036854775808;
CREATE SEQUENCE SE_NAME_17 INCREMENT BY -9223372036854775809;
CREATE SEQUENCE SE_NAME_18 START WITH 9223372036854775808;

-- 转换后PostgreSQL SQL:
CREATE SEQUENCE SE_NAME MAXVALUE 100000 CYCLE
CREATE SEQUENCE SE_NAME_1 NO CYCLE
CREATE SEQUENCE SE_NAME_2 CACHE 20
CREATE SEQUENCE SE_NAME_3 START WITH 1
CREATE SEQUENCE SE_NAME_4 START WITH 1
CREATE SEQUENCE SE_NAME_5 START WITH 1
CREATE SEQUENCE SE_NAME_6 START WITH 1
CREATE SEQUENCE SE_NAME_7 START WITH 1
CREATE SEQUENCE SE_NAME_8 NO MINVALUE MAXVALUE 9223372036854775807
CREATE SEQUENCE SE_NAME_9 MAXVALUE 9223372036854775806
CREATE SEQUENCE SE_NAME_10 MINVALUE -9223372036854775808 NO MAXVALUE
CREATE SEQUENCE SE_NAME_11 MINVALUE -9223372036854775808
CREATE SEQUENCE SE_NAME_12 MINVALUE -9223372036854775808
CREATE SEQUENCE SE_NAME_13 INCREMENT BY 9223372036854775807
CREATE SEQUENCE SE_NAME_14 INCREMENT BY -9223372036854775808
CREATE SEQUENCE SE_NAME_15 MINVALUE -9223372036854775808
CREATE SEQUENCE SE_NAME_16 INCREMENT BY 9223372036854775807
CREATE SEQUENCE SE_NAME_17 INCREMENT BY -9223372036854775808
CREATE SEQUENCE SE_NAME_18 START WITH 9223372036854775807

3.2.5.1.17. 提交当前事务

COMMIT

描述

提交当前事务，将所有对数据的更改永久保存。

示例

-- 转换前Oracle SQL:
CREATE TABLE commit_test (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);
INSERT INTO commit_test (id, name) VALUES (1, 'Alice');
INSERT INTO commit_test (id, name) VALUES (2, 'Bob');
COMMIT;
SELECT * FROM commit_test;

-- 转换后PostgreSQL SQL:
CREATE TABLE commit_test (id decimal PRIMARY KEY,name varchar(50));
INSERT INTO commit_test (id,name) VALUES (1,'Alice');
INSERT INTO commit_test (id,name) VALUES (2,'Bob');
COMMIT;
SELECT * FROM commit_test;
id|name |
--+-----+
1|Alice|
2|Bob  |