安裝 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 數據庫兼容的特性
數據類型
| 數據類型 | 說明 |
|---|---|
| VARCHAR2 | 可變長度字符數據類型 |
| NVARCHAR2 | 可變長度國家字符數據類型 |
| DATE | 存儲日期和時間的數據類型 |
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 函數
- 數學函數
| 函數名稱 | 說明 | 對比pg13 |
|---|---|---|
| BITAND | Performs a bitwise AND operation | 增強 |
| COSH | Calculates the hyperbolic cosine of a number | 自帶 |
| SINH | Calculates the hyperbolic sine of a number | 自帶 |
| TANH | Calculates the hyperbolic tangent of a number | 自帶 |
- 字符串函數
| 函數名稱 | 說明 | 對比pg13 |
|---|---|---|
| INSTR | Returns the position of a substring in a string | 新增 |
| LENGTH | Returns the length of a string in number of characters | 增強 |
| LENGTHB | Returns the length of a string in number of bytes | 新增 |
| LPAD | Left-pads a string to a specified length with a sequence of characters | 增強 |
| LTRIM | Removes the specified characters from the beginning of a string | 增強 |
| NLSSORT | Returns a byte string used to sort strings in linguistic sort sequence based on locale | 新增 |
| REGEXP_COUNT | searches a string for a regular expression, and returns a count of the matches | 新增 |
| REGEXP_INSTR | returns the beginning or ending position within the string where the match for a pattern was located | 新增 |
| REGEXP_LIKE | condition in the WHERE clause of a query, causing the query to return rows that match the given pattern | 新增 |
| REGEXP_SUBSTR | returns the string that matches the pattern specified in the call to the function | 新增 |
| REGEXP_REPLACE | replace substring(s) matching a POSIX regular expression | 增強 |
| RPAD | Right-pads a string to a specified length with a sequence of characters | 增強 |
| RTRIM | Removes the specified characters from the end of a string | 增強 |
| SUBSTR | Extracts part of a string using characters to specify position and length | 增強 |
| SUBSTRB | Extracts part of a string using bytes to specify position and length | 新增 |
- Date/time 函數
| 函數名稱 | 說明 | 對比pg13 |
|---|---|---|
| ADD_MONTHS | Adds months to a date | 新增 |
| DBTIMEZONE | Returns the value of the database time zone | 新增 |
| LAST_DAY | Returns the last day of the month in which the specified date falls | 新增 |
| MONTHS_BETWEEN | Returns the number of months between two dates | 新增 |
| NEXT_DAY | Returns the date of the first instance of a particular day of the week that follows the specified date | 新增 |
| ROUND | Rounds a date | 增強 |
| SESSIONTIMEZONE | Returns the time zone of the session | 新增 |
| SYSDATE | Returns the system date | 新增 |
| TRUNC | Truncates a date | 增強 |
- 數據類型格式化函數
| 函數名稱 | 說明 | 對比pg13 |
|---|---|---|
| TO_CHAR | Converts a value to a string | 增強 |
| TO_DATE | Converts a string to a date in accordance with the specified format | 增強 |
| TO_MULTI_BYTE | Converts a single-byte string to a multibyte string | 新增 |
| TO_NUMBER | Converts a value to a number in accordance with the specified format | 增強 |
| TO_SINGLE_BYTE | Converts a multibyte string to a single-byte string | 新增 |
- 條件表達式函數
| 函數名稱 | 說明 | 對比pg13 |
|---|---|---|
| DECODE | Compares values, and if they match, returns a corresponding value | 增強 |
| GREATEST | Returns the greatest of the list of one or more expressions | 增強 |
| LEAST | Returns the least of the list of one or more expressions | 增強 |
| LNNVL | Evaluates if a value is false or unknown | 新增 |
| NANVL | Returns a substitute value when a value is not a number (NaN) | 新增 |
| NVL | Returns a substitute value when a value is NULL | 新增 |
| NVL2 | Returns a substitute value based on whether a value is NULL or not NULL | 新增 |
- 聚合函數
| 函數名稱 | 說明 | 對比pg13 |
|---|---|---|
| LISTAGG | Returns a concatenated, delimited list of string values | 新增 |
| MEDIAN | Calculates the median of a set of values | 新增 |
- 返回內部信息的函數
| 函數名稱 | 說明 | 對比pg13 |
|---|---|---|
| DUMP | Returns internal information of a value | 新增 |
SQL 運算符
| 名稱 | 說明 | 對比pg13 |
|---|---|---|
| Datetime operator | Datetime operator for the DATE type | - |
Packages 包
| 名稱 | 說明 | 對比pg13 |
|---|---|---|
| DBMS_ALERT | Sends alerts to multiple sessions | - |
| DBMS_ASSERT | Validates the properties of an input value | - |
| DBMS_OUTPUT | Sends messages to clients | - |
| DBMS_PIPE | Creates a pipe for inter-session communication | - |
| DBMS_RANDOM | Generates random numbers | - |
| DBMS_UTILITY | Provides various utilities | - |
| UTL_FILE | Enables text file operations | - |
使用 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)
最后修改時間：2022-04-02 11:34:09
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
