oracle移植到postgres

最新推荐文章于 2025-06-18 14:01:06 发布

zm_21

最新推荐文章于 2025-06-18 14:01:06 发布

阅读量3.2k

点赞数

分类专栏： DB 文章标签： oracle postgres

DB 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了Oracle数据库向PostgreSQL数据库迁移过程中常见的数据类型映射、语法差异及解决方案，包括序列号获取、空字符串处理、触发器实现等方面的内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

类型：
varchar2 -> varchar
number -> numeric
sysdate -> now()
clob -> text/BYTEA
BLOB -> BYTEA

1. postgres必须使用AS来定义别名。
2. sysdate -> now()
3. oracle和postgres都是永nextval来获取下一个序列号，但使用方法不一样
oracle: ###_seq.nextval
postgres: nextval('###_seq');
oracle:
select seq_name.nextval from dual
postgres:
select nextval('seq_name');
4. postgres不再允许空字符串''作为空值null直接插入数字类型的列中.会出现错误信息：
ERROR: invalid input syntax for integer: ""
　　比较标准的解决方法是：
　　1、在应用程序中控制（废话）
　　2、在要插入的表上做触发器，自动将''转换为0或null
　　3、自定义数据类型，在输入输出函数中自动将''转换为0或null
　　4、自定义函数，在应用程序中套在可能会产生''的字段外面
　　函数举例：
　　esin(enpty string is null)
　　CREATE OR REPLACE FUNCTION esin(text)
　　RETURNS int4 AS
　　$BODY$
　　SELECT CASE
　　WHEN $1='' THEN null
　　ELSE $1::integer
　　END;
　　$BODY$
　　LANGUAGE 'sql' VOLATILE;
　　ALTER FUNCTION esin(text) OWNER TO postgres;
5. oracle中的clob对应postgres中的bytea(也可以是oid)
  oracle对clob字段的操作很麻烦，
  postgres对bytea(字段的操作则和其他类型的字段一样操作。
6. oracle中时间运算中时间字段可以和整型数直接相加减，如SYSDATE + 11 .当前时间加11天
  postgres中必须强制转化为interval才能参与时间运算，如SELECT now()+CAST(to_char(11,'99')||' days' AS interval)
7. postgres中存储过程，触发器都以函数体的形式存在，而oracle中触发器，存储过程，函数单独存在。
8. 正因为postgres中触发器都以函数体的形式存在，所以，触发器对应的函数必须返回TRIGGER。
postgres:
CREATE FUNCTION pg_fct_trg_cpg_assignment () RETURNS TRIGGER AS $$
BEGIN
   IF (TG_OP = 'DELETE') THEN
     UPDATE Messauftrag SET Status='NEU' WHERE Status='AKTIV' AND ( cpg_id=OLD.id );
     RETURN OLD;
   ELSIF (TG_OP = 'UPDATE') THEN
     UPDATE Messauftrag SET Status='NEU' WHERE Status='AKTIV' AND ( cpg_id=NEW.id );
     RETURN NEW;
   ELSIF (TG_OP = 'INSERT') THEN
     UPDATE Messauftrag SET Status='NEU' WHERE Status='AKTIV' AND ( cpg_id=NEW.id );
     RETURN NEW;
   END IF;
   RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_cpg_assignment
AFTER INSERT OR UPDATE OR DELETE ON "cpg_assignment" FOR EACH ROW
EXECUTE PROCEDURE pg_fct_trg_cpg_assignment();

从pg_fct_trg_cpg_assignment函数可以发现，postgres对NEW和OLD的引用受到触发器操作的限制,
insert时，只能使用NEW;
delete时，只能使用OLD;
update时，可以使用NEW和OLD;
而oracle不管什么操作，引用NEW和OLD都不会产生语法错误。
oracle:
CREATE OR REPLACE TRIGGER trg_cpg_assignment
AFTER INSERT OR DELETE OR UPDATE ON CPG_ASSIGNMENT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE Messauftrag SET Status='NEU' WHERE Status='AKTIV' AND ( cpg_id=:NEW.id OR cpg_id=:OLD.id );
END;

---------------------------------------------------
ora2pg使用
onwww.webperform.comserver, Ora2Pg.pm locate
/home/dm/ora2pg/Ora2Pg.pm
download ora2pg-4.8.tar.gz fromhttp://pgfoundry.org/frs/?group_id=1000312&release_id=1216
解压：tar -zxvf ora2pg-4.8.tar.gz

需要zlib.pm
apt-get install libcompress-zlib-perl
apt-get install libstring-random-perl

怎样从oracle将数据导入到postgres
1. 创建postgres数据库，运行脚本，tables.sql，sequences.sql，functions.sql，triggers.sql，procedures.sql
如果functions.sql执行出错，需要添加plpgsql, createlang -U postgres plpgsql webperf
2. 执行脚本：oracle_data_export.pl 将数据导出为: config.copy.bz2
3. 解压 bunzip2 config.copy.bz2
4. 删除所有的trigger函数
5. \i config.copy 导入所有数据
6. 重新添加trigger函数

原文链接： http://www.54xue.com/w/54/n-2854.html