IvorySQL 3.0 模式下执行创建约束报错 #612
Description
Bug Report
IvorySQL Version
3.0
OS Version (uname -a)
Centos7.9
[ivorysql@ivorysqldb ~]$ psql -p 1521 postgres
psql (16.0)
Type "help" for help.
-- 可以看到熟悉的dual虚拟表
postgres=# select * from dual;
dummy
X
(1 row)
postgres=# select sysdate,systimestamp from dual;
sysdate | systimestamp
------------+-----------------------------------
2024-01-09 | 2024-01-09 11:11:55.758130 +08:00
(1 row)
postgres=# CREATE TABLE CORE_PRODUCT
postgres-# (
postgres(# COM_ID NUMBER(10) NOT NULL,
postgres(# PROD_ID NUMBER(10) NOT NULL,
postgres(# OTHER_CAT_CODE VARCHAR2(255 BYTE),
postgres(# PROD_KEYWORD VARCHAR2(500 BYTE) NOT NULL,
postgres(# PROD_PRICE VARCHAR2(50 BYTE) DEFAULT '0',
postgres(# PROD_CUSTOM VARCHAR2(4000 BYTE),
postgres(# PROD_DESCRIPT VARCHAR2(4000 BYTE) NOT NULL,
postgres(# RECOMMEND VARCHAR2(1 BYTE) DEFAULT '0' NOT NULL,
postgres(# PHOTO_EXI_FLAG VARCHAR2(1 BYTE) DEFAULT '0',
postgres(# PREP_TIME DATE DEFAULT TO_DATE('1900-01-01','YYYY-MM-DD HH24:MI:SS'),
postgres(# UPDATE_TIME DATE DEFAULT TO_DATE('1900-01-01','YYYY-MM-DD HH24:MI:SS') NOT NULL,
postgres(# ADD_TIME DATE DEFAULT TO_DATE('1900-01-01','YYYY-MM-DD HH24:MI:SS') NOT NULL,
postgres(# COM_NAME VARCHAR2(255 BYTE) DEFAULT ' ',
postgres(# REP_TIME TIMESTAMP(6) WITH TIME ZONE DEFAULT TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') NOT NULL,
postgres(# LAST_CHECK_TIME DATE DEFAULT TO_DATE('1900-01-01','YYYY-MM-DD HH24:MI:SS')
postgres(# );
CREATE TABLE
postgres=#
postgres=# ALTER TABLE CORE_PRODUCT ADD (
postgres(# CONSTRAINT PK_COREPRODSEARCH
postgres(# PRIMARY KEY(PROD_ID));
ERROR: syntax error at or near "CONSTRAINT"
LINE 2: CONSTRAINT PK_COREPRODSEARCH
^
postgres=#
postgres=# CREATE OR REPLACE TRIGGER REP_CORE_PRODUCT
postgres-# BEFORE
postgres-# INSERT OR UPDATE ON CORE_PRODUCT FOR EACH ROW
postgres-# BEGIN
postgres-# IF :OLD.REP_TIME IS NULL OR :OLD.REP_TIME < SYSTIMESTAMP THEN
postgres-# :NEW.REP_TIME := SYSTIMESTAMP;
ERROR: syntax error at or near "BEGIN"
LINE 4: BEGIN ...
^
postgres=# ELSE
postgres-# :NEW.REP_TIME := :OLD.REP_TIME + 1 / 86400;
ERROR: syntax error at or near "ELSE"
LINE 1: ELSE ...
^
postgres=# END IF;
ERROR: syntax error at or near "IF"
LINE 1: END IF;
^
postgres=# END;
WARNING: there is no transaction in progress
COMMIT
postgres=# /
postgres=# alter table core_product add (
postgres(# constraint pk_coreprodsearch
postgres(# primary key(prod_id));
ERROR: syntax error at or near "constraint"
LINE 2: constraint pk_coreprodsearch
-- 通过上面测试，可以看到Oracle兼容模式下支持查询dual，而且对TIMESTAMP WITH TIME ZONE也是支持的
-- 但当前不支持触发器创建
-- 竟然添加约束也是报错，而且SQL语句换成小写也是报错
-- 若是换成如下SQL可以正常执行
postgres=# alter table core_product add constraint pk_coreprodsearch primary key(prod_id);
ALTER TABLE
postgres=#