继续我的上一篇文章 - "将预言机迁移到 postgresql 用于编码"UTF8"的无效字节序列:0x00">
我正在尝试将远程 Oracle 表中的数据插入本地 PostgreSQL 表(通过oracle_fdw扩展名(。我的 Oracle 表有一个名为 street 的列,它有有效的字符串值,有时还有下一个无效的(在 PostgreSQL 中(字符串:' ' (空格(。
当我尝试复制列值时,我收到上面和上一篇文章中提到的错误。我知道在将oracle数据插入PostgreSQL之前,我需要更改它。我必须即时完成,所以我尝试在PostgreSQL中搜索oracle解码func。我找到了两个解决方案,我使用了它们,但我得到了相同的错误:
1.使用带大小写的选择:
mydb=>select *,(case when v.street=' ' then null END) from customer_prod v;
ERROR: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: converting column "street" for foreign table scan of
"customer_prod", row 254148
2.使用ORAFCE扩展的解码功能:
mydb=>select decode(street,' ',null) from customer_prod;
ERROR: invalid byte sequence for encoding "UTF8": 0x00
所以,我仍然收到错误。如何解决此问题?
当值从 Oracle 传输到 PostgreSQL 时会发生错误,因此后处理不会阻止错误。
为了便于演示,让我们创建一个显示问题的 Oracle 表:
CREATE TABLE nulltest(
id number(5) CONSTRAINT nulltest_pkey PRIMARY KEY,
val varchar2(10 CHAR)
);
INSERT INTO nulltest VALUES (1, 'schön');
INSERT INTO nulltest VALUES (2, 'bö' || CHR(0) || 'se');
INSERT INTO nulltest VALUES (3, 'egal');
COMMIT;
让我们在 PostgreSQL 中为它创建一个外表:
CREATE FOREIGN TABLE nulltest (
id integer OPTIONS (key 'true') NOT NULL,
val varchar(10)
) SERVER oracle
OPTIONS (table 'NULLTEST');
SELECT * FROM nulltest;
ERROR: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: converting column "val" for foreign table scan of "nulltest", row 2
现在最简单的方法是创建一个过滤掉零字符的外表:
CREATE FOREIGN TABLE filter_nulltest (
id integer OPTIONS (key 'true') NOT NULL,
val varchar(10)
) SERVER oracle
OPTIONS (table '(SELECT id, replace(val, CHR(0), NULL) FROM nulltest)');
SELECT * FROM filter_nulltest;
┌────┬───────┐
│ id │ val │
├────┼───────┤
│ 1 │ schön │
│ 2 │ böse │
│ 3 │ egal │
└────┴───────┘
(3 rows)
另一个效率较低的选择是创建一个函数来捕获并向您报告坏行,以便您可以在 Oracle 端修复它们:
CREATE OR REPLACE FUNCTION get_nulltest() RETURNS SETOF nulltest
LANGUAGE plpgsql AS
$$DECLARE
v_id integer;
n nulltest;
BEGIN
FOR v_id IN SELECT id FROM nulltest
LOOP
BEGIN
SELECT nulltest.* INTO n
FROM nulltest
WHERE id = v_id;
RETURN NEXT n;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Caught error % for id=%: %', SQLSTATE, v_id, SQLERRM;
END;
END LOOP;
END;$$;
SELECT * FROM get_nulltest();
NOTICE: Caught error 22021 for id=2: invalid byte sequence for encoding "UTF8": 0x00
┌────┬───────┐
│ id │ val │
├────┼───────┤
│ 1 │ schön │
│ 3 │ egal │
└────┴───────┘
(2 rows)