暂无图片
暂无图片
9
暂无图片
暂无图片
暂无图片

PostgreSQL插件orafce--安装以及简单介绍

原创 张玉龙 2022-04-01
6309

安装 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 中实现的功能

  • 数据类型
    image.png

  • Function
    image.png

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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论