安裝 orafce

orafce 是 PostgreSQL 的一個擴展，主要是為了在 PostgreSQL 中兼容 Oracle 的部分語法、數據類型、函數、字典表等，有了 orafce 可以對使用 Oracle 數據的應用程序更加方便的移植到 PostgreSQL 數據庫上，盡可能的減少應用程序的代碼改動量，從而簡化了許多遷移工作量。

orafce 的源碼地址：https://github.com/orafce/orafce

[root@pgtest1 soft]# tar -xvf orafce-VERSION_3_18_1.tar.gz
[root@pgtest1 soft]# cd orafce-VERSION_3_18_1
[root@pgtest1 orafce-VERSION_3_18_1]# make
[root@pgtest1 orafce-VERSION_3_18_1]# make install
[root@pgtest1 orafce-VERSION_3_18_1]# psql -c "CREATE EXTENSION orafce;"

這樣就安裝完成了，但是需要注意時區問題，orafce 默認使用的是 GMT 時區，由參數單獨的參數 orafce.timezone 控制，即使 timezone 參數設置為 PRC ，執行 sysdate() 函數返回的時間也會與系統相差8小時。

[root@pgtest1 ~]# psql

postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |       clock_timestamp
---------------------+-------------------------------+-------------------------------+-----------------------------
 2022-04-01 02:31:53 | 2022-04-01 10:31:52.596967+08 | 2022-04-01 10:31:52.596967+08 | 2022-04-01 10:31:52.5971+08
(1 row)

postgres=#  show timezone;
 TimeZone
----------
 PRC
(1 row)

postgres=# show orafce.timezone;
 orafce.timezone
-----------------
 GMT
(1 row)

調整參數 orafce.timezone

[root@pgtest1 ~]# vi $PGDATA/postgresql.conf
log_timezone = 'PRC'
timezone = 'PRC'
orafce.timezone = 'PRC'

[root@pgtest1 ~]# systemctl restart postgres-13.service
[root@pgtest1 ~]# psql
postgres=# show orafce.timezone;
 orafce.timezone
-----------------
 PRC
(1 row)

postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |        clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
 2022-04-01 10:32:42 | 2022-04-01 10:32:41.543877+08 | 2022-04-01 10:32:41.543877+08 | 2022-04-01 10:32:41.543979+08
(1 row)

與 Oracle 數據庫兼容的特性

數據類型

postgres=# set search_path="$user", public, oracle;
SET
postgres=# CREATE TABLE tt (
postgres(#  name VARCHAR2(64) NOT NULL,
postgres(#  status NVARCHAR2(2000),
postgres(#  update_time DATE default (SYSDATE())
postgres(# );
CREATE TABLE

postgres=# \d tt
                        Table "public.tt"
   Column    |      Type       | Collation | Nullable |  Default
-------------+-----------------+-----------+----------+-----------
 name        | varchar2(64)    |           | not null |
 status      | nvarchar2(2000) |           |          |
 update_time | date            |           |          | sysdate()

# 測試在 date 數據類型的字段上創建分區表
create table test_range(id serial, create_time date) partition by range(create_time);
create table test_range_20220301 PARTITION of test_range FOR VALUES FROM ('2022-03-01 00:00:00') TO ('2022-03-02 00:00:00');
create table test_range_20220302 PARTITION of test_range FOR VALUES FROM ('2022-03-02 00:00:00') TO ('2022-03-03 00:00:00');
create table test_range_20220303 PARTITION of test_range FOR VALUES FROM ('2022-03-03 00:00:00') TO ('2022-03-04 00:00:00');
create table test_range_20220304 PARTITION of test_range FOR VALUES FROM ('2022-03-04 00:00:00') TO ('2022-03-05 00:00:00');
create table test_range_20220305 PARTITION of test_range FOR VALUES FROM ('2022-03-05 00:00:00') TO ('2022-03-06 00:00:00');
create table test_range_default partition of test_range default;

postgres=# \d+ test_range
                                            Partitioned table "public.test_range"
   Column    |  Type   | Collation | Nullable |                Default                 | Storage | Stats target | Description
-------------+---------+-----------+----------+----------------------------------------+---------+--------------+-------------
 id          | integer |           | not null | nextval('test_range_id_seq'::regclass) | plain   |              |
 create_time | date    |           |          |                                        | plain   |              |
Partition key: RANGE (create_time)
Partitions: test_range_20220301 FOR VALUES FROM ('2022-03-01') TO ('2022-03-02'),
            test_range_20220302 FOR VALUES FROM ('2022-03-02') TO ('2022-03-03'),
            test_range_20220303 FOR VALUES FROM ('2022-03-03') TO ('2022-03-04'),
            test_range_20220304 FOR VALUES FROM ('2022-03-04') TO ('2022-03-05'),
            test_range_20220305 FOR VALUES FROM ('2022-03-05') TO ('2022-03-06'),
            test_range_default DEFAULT

# 向分區表中插入數據
postgres=# insert into test_range (create_time) values (sysdate());
INSERT 0 1

# 查詢分區表里的數據
postgres=# select * from test_range;
 id | create_time
----+-------------
  2 | 2022-03-31
(1 row)

postgres=# select to_char(create_time,'YYYY-MM-DD HH24:MI:SS') from test_range;
       to_char
---------------------
 2022-03-31 00:00:00
(1 row)

為什么這里的 date 數據類型似乎只存儲 ‘年月日’，而 Oracle 的 date 數據類型會存儲 ‘年月日 時分秒’。
這里就要說一下使用 orafce 的注意事項，詳見后面章節<使用 orafce 的注意事項>。

支持 DUAL 表

postgres=# \d+ dual
                                  View "public.dual"
 Column |       Type        | Collation | Nullable | Default | Storage  | Description
--------+-------------------+-----------+----------+---------+----------+-------------
 dummy  | character varying |           |          |         | extended |
View definition:
 SELECT 'X'::character varying AS dummy;

postgres=# \dv public.*
               List of relations
 Schema |        Name        | Type |  Owner
--------+--------------------+------+----------
 public | dual               | view | postgres
 public | pg_stat_statements | view | postgres
(2 rows)

postgres=# select 1 from dual;
 ?column?
----------
        1
(1 row)

postgres=# select * from dual;
 dummy
-------
 X
(1 row)

postgres=# SELECT CURRENT_DATE "date" FROM DUAL;
    date
------------
 2022-04-01

SQL 函數

• 數學函數

• 字符串函數

• Date/time 函數

• 數據類型格式化函數

• 條件表達式函數

• 聚合函數

• 返回內部信息的函數

SQL 運算符

Packages 包

使用 orafce 的注意事項

在上面章節測試 date 數據類型時，發現一個問題，為什么這里的 date 數據類型似乎只存儲 ‘年月日’，而 Oracle 的 date 數據類型會存儲 ‘年月日 時分秒’。

orafce 提供的兼容 oracle 的函數被定義在創建數據庫集群時默認創建的 “public” 模式中，因此它們可供所有用戶使用，無需特殊設置，但是需要確保 “public”（不帶雙引號）包含在 search_path 參數中指定的模式搜索路徑列表中。

orafce 提供的以下功能在 PostgreSQL 和 orafce 中使用不同的外部規范實現。在 PostgreSQL 的默認配置中，PostgreSQL 的標準特性優先。

使用不同的外部規范在 PostgreSQL 和 orafce 中實現的功能

• 數據類型
  

• Function
  

PostgreSQL 默認配置中不能使用的特性

• Function
  • SYSDATE
  • DBTIMEZONE
  • SESSIONTIMEZONE
  • TO_CHAR (date/time value)
• Operator
  • Datetime operator

要使用這些功能，請在 postgresql.conf 的 “search_path” 參數中設置 “oracle” 和 “pg_catalog”。執行此操作時，必須在 “pg_catalog” 之前指定 “oracle”。

search_path = '"$user", public, oracle, pg_catalog'

• search_path 參數指定搜索模式的順序。與 Oracle 數據庫兼容的每個特性都在 oracle 模式中定義。

• 建議在 postgresql.conf 中設置 search_path。在這種情況下，它將對每個實例都有效。

• search_path 的配置可以在用戶級別或數據庫級別進行。設置示例如下所示。

• 如果 PostgreSQL 的標準特性優先，并且不需要使用 PostgreSQL 的默認配置不能使用的特性，則無需更改 search_path 的設置。

• 用戶級別的設置示例

  • 這可以通過執行 SQL 命令來設置。在此示例中，user1 用作用戶名。

ALTER USER user1 SET search_path = "$user",public,oracle,pg_catalog;

• 數據庫級別設置示例
  • 這可以通過執行 SQL 命令來設置。在此示例中，db1 用作數據庫名稱。
  • 必須在 “pg_catalog” 之前指定 “oracle”。

ALTER DATABASE db1 SET search_path = "$user",public,oracle,pg_catalog;

再次測試 date 數據類型，看看是否真的支持存儲 ‘年月日 時分秒’

# 修改參數 search_path ，必須在 "pg_catalog" 之前指定 "oracle"
[root@pgtest1 ~]# vi $PGDATA/postgresql.conf
search_path = '"$user", public, oracle, pg_catalog'

[root@pgtest1 ~]# systemctl restart postgres-13.service

drop table test_range;
create table test_range(id serial, create_time date) partition by range(create_time);
create table test_range_20220301 PARTITION of test_range FOR VALUES FROM ('2022-03-01 00:00:00') TO ('2022-03-02 00:00:00');
create table test_range_20220302 PARTITION of test_range FOR VALUES FROM ('2022-03-02 00:00:00') TO ('2022-03-03 00:00:00');
create table test_range_20220303 PARTITION of test_range FOR VALUES FROM ('2022-03-03 00:00:00') TO ('2022-03-04 00:00:00');
create table test_range_20220304 PARTITION of test_range FOR VALUES FROM ('2022-03-04 00:00:00') TO ('2022-03-05 00:00:00');
create table test_range_20220305 PARTITION of test_range FOR VALUES FROM ('2022-03-05 00:00:00') TO ('2022-03-06 00:00:00');
create table test_range_default partition of test_range default;

postgres=# \d+ test_range
                                            Partitioned table "public.test_range"
   Column    |  Type   | Collation | Nullable |                Default                 | Storage | Stats target | Description
-------------+---------+-----------+----------+----------------------------------------+---------+--------------+-------------
 id          | integer |           | not null | nextval('test_range_id_seq'::regclass) | plain   |              |
 create_time | date    |           |          |                                        | plain   |              |
Partition key: RANGE (create_time)
Partitions: test_range_20220301 FOR VALUES FROM ('2022-03-01 00:00:00') TO ('2022-03-02 00:00:00'),
            test_range_20220302 FOR VALUES FROM ('2022-03-02 00:00:00') TO ('2022-03-03 00:00:00'),
            test_range_20220303 FOR VALUES FROM ('2022-03-03 00:00:00') TO ('2022-03-04 00:00:00'),
            test_range_20220304 FOR VALUES FROM ('2022-03-04 00:00:00') TO ('2022-03-05 00:00:00'),
            test_range_20220305 FOR VALUES FROM ('2022-03-05 00:00:00') TO ('2022-03-06 00:00:00'),
            test_range_default DEFAULT

# 這里就看出了不一樣，Partitions 中 FROM 和 TO 的時間有 '時分秒' 了('2022-03-01 00:00:00')。
# 向分區表中插入數據
postgres=# insert into test_range (create_time) values (sysdate());
INSERT 0 1

# 查詢分區表里的數據，顯示的和 Oracle 一樣了
postgres=# select * from test_range;
 id |     create_time
----+---------------------
  1 | 2022-04-01 10:08:18
(1 row)