AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之五：函数的差异(七)

2016-08-26 5336
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。

1、ADD_MONTHS函数

ADD_MONTHS(date,integer) 是Oracle的一个时间运算函数。返回参数date日期的integer个月后的日期。是Oracle日期运算的一个很基本的函数。
PostgreSQL里面没有这个函数。可以使用Interval数据类型的加减来模拟。返回的是一个timestamp的值。也可以自己创建函数来实现它。

Oracle ADD_MONTHS

SQL> select sysdate  from dual;

SYSDATE
--------------
24-8月 -16

SQL> select add_months(sysdate, 2) from dual;

ADD_MONTHS(SYS
--------------
24-10月-16

SQL> select add_months(sysdate, -2) from dual;

ADD_MONTHS(SYS
--------------
24-6月 -16

SQL> select add_months(to_date('2016/08/01', 'YYYY/MM/DD'), -1) from dual;

ADD_MONTHS(TO_
--------------
01-7月 -16

PostgreSQL ADD_MONTHS

postgres=# select now();
             now
------------------------------
 2016-08-24 09:30:10.87367+08
(1 行记录)

postgres=# select add_months(now(),2);
错误:  函数 add_months(timestamp with time zone, integer) 不存在
第1行select add_months(now(),2);
            ^
提示:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
postgres=# select now() + '2 month';
           ?column?
-------------------------------
 2016-10-24 09:30:35.284043+08
(1 行记录)

postgres=# select now() + '-2 month';
           ?column?
------------------------------
 2016-06-24 09:30:53.23845+08
(1 行记录)

postgres=# select to_timestamp('2016-08-01','YYYY-MM-DD') + '-1 month';
        ?column?
------------------------
 2016-07-01 00:00:00+08
(1 行记录)

postgres=# CREATE FUNCTION add_months(date, int)
postgres-#  RETURNS date AS
postgres-#  'SELECT ($1 + ( $2::text || ''months'')::interval)::date;'
postgres-#  LANGUAGE 'sql';
CREATE FUNCTION
postgres=# select add_months(current_date, 2);
 add_months
------------
 2016-10-24
(1 行记录)

postgres=# select add_months(current_date, -2);
 add_months
------------
 2016-06-24
(1 行记录)

postgres=# select add_months('2016/08/01', -1);
 add_months
------------
 2016-07-01
(1 行记录)

2、LAST_DAY 函数

LAST_DAY(date)函数是Oracle的一个取日期的月末日的函数。
PostgreSQL中没有对应的日期函数。可以自己使用Interval来实现。也可以自己创建函数来实现。

Oracle LAST_DAY

SQL> select sysdate from dual;

SYSDATE
--------------
24-8月 -16

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA
--------------
31-8月 -16

SQL> select last_day(to_date('2016/09/16', 'YYYY/MM/DD')) from dual;

LAST_DAY(TO_DA
--------------
30-9月 -16

PostgreSQL LAST_DAY

postgres=# CREATE FUNCTION LAST_DAY(DATE)
postgres-#  RETURNS DATE AS
postgres-#          'SELECT date(substr(text($1::timestamp +
postgres'#                  ''1 month''),1,7)||''-01'')-1'
postgres-#          LANGUAGE 'sql';
CREATE FUNCTION
postgres=# select current_date;
    date
------------
 2016-08-24
(1 行记录)

postgres=# select last_day(current_date);
  last_day
------------
 2016-08-31
(1 行记录)

postgres=# select last_day('2016/09/16'::date);
  last_day
------------
 2016-09-30
(1 行记录)

3、MONTHS _BETWEEN函数

MONTHS _BETWEEN函数是Oracle的一个判断两个日期之间有几个月的间隔的函数。
PostgreSQL中也没有对应的函数，只能自己通过函数实现。不过由于对于相差月份的算法不一样，结果可能有些差异。下面给出一个例子。

Oracle MONTHS _BETWEEN

SQL> SELECT MONTHS_BETWEEN(TO_DATE('02-02-1995','MM-DD-YYYY'),
  2   TO_DATE('01-01-1995','MM-DD-YYYY')) MONTHS from dual;

    MONTHS
----------
1.03225806

SQL> SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('01-01-2014','MM-DD-YYYY')) MONTHS from dual;

    MONTHS
----------
31.7571192

PostgreSQL MONTHS _BETWEEN

postgres=# CREATE FUNCTION MONTHS_BETWEEN(DATE,DATE)
postgres-#  RETURNS NUMERIC AS
postgres-#          'SELECT to_number(text(date($1)-
postgres'#                             date($2)),''999999999'')/31'
postgres-#          LANGUAGE 'sql';
CREATE FUNCTION
postgres=# SELECT MONTHS_BETWEEN('1995-02-02'::date,'1995-01-01'::date);
   months_between
--------------------
 1.0322580645161290
(1 行记录)

postgres=# SELECT MONTHS_BETWEEN(CURRENT_DATE, '2014-01-01'::DATE) MONTHS;
       months
---------------------
 31.1612903225806452
(1 行记录)

4、EXTRACT函数

EXTRACT是Oracle用于从一个date、timestamp或者interval类型中截取到特定的部分所用的函数。Oracle只支持截取年、月、日、时、分、秒，时区时，时区分、时区等。
PostgreSQL也支持该函数， 截取的东西比Oracle的多一些，但是不支持时区时，时区分和时区的截取（文档中说是支持的，但是我的实际测试中不支持）。

Oracle EXTRACT

SQL> select extract(year from date'2011-05-17') year from dual;

      YEAR
----------
      2011

SQL> select extract(month from date'2011-05-17')  from dual;

EXTRACT(MONTHFROMDATE'2011-05-17')
----------------------------------
                                 5

SQL> select extract(day from date'2011-05-17')  from dual;

EXTRACT(DAYFROMDATE'2011-05-17')
--------------------------------
                              17

SQL> select extract(hour from timestamp'2011-05-17 12:56:20.1234') AAA from dual;

       AAA
----------
        12

SQL> select extract(minute from timestamp'2011-05-17 12:56:20.1234') part from dual;

      PART
----------
        56

SQL> select extract(second from timestamp'2011-05-17 12:56:20.1234') part from dual;

      PART
----------
   20.1234

SQL> select extract(timezone_region from systimestamp) part from dual;

PART
----------------------------------------------------------------
UNKNOWN

SQL> select extract(timezone_abbr from systimestamp) part from dual;

PART
----------
UNK

SQL> select extract(timezone_hour from systimestamp) part from dual;

      PART
----------
         8

SQL> select extract(timezone_minute from systimestamp) part from dual;

      PART
----------
         0

PostgreSQL EXTRACT

postgres=# select extract(year from date'2011-05-17') part;
 part
------
 2011
(1 行记录)

postgres=# select extract(month from date'2011-05-17') part;
 part
------
    5
(1 行记录)

postgres=# select extract(day from date'2011-05-17') part;
 part
------
   17
(1 行记录)

postgres=# select extract(week from date'2011-05-17') part;
 part
------
   20
(1 行记录)

postgres=# select extract(century from date'2011-05-17') part;
 part
------
   21
(1 行记录)

postgres=# select extract(hour from '2011-05-17 12:56:20.1234'::timestamp) AAA;
 aaa
-----
  12
(1 行记录)

postgres=# select extract(minute from '2011-05-17 12:56:20.1234'::timestamp) AAA;
 aaa
-----
  56
(1 行记录)

postgres=# select extract(second from '2011-05-17 12:56:20.1234'::timestamp) AAA;
   aaa
---------
 20.1234
(1 行记录)

postgres=# select extract(microsecond from '2011-05-17 12:56:20.1234'::timestamp) AAA;
   aaa
----------
 20123400
(1 行记录)

postgres=# select extract(isodow from '2011-05-17 12:56:20.1234'::timestamp) AAA;
 aaa
-----
   2
(1 行记录)

postgres=# select extract(quarter from '2011-05-17 12:56:20.1234'::timestamp) AAA;
 aaa
-----
   2
(1 行记录)

postgres=# select extract(timezone  from '2011-05-17 12:56:20.1234'::timestamp) AAA;
错误:  不支持时间戳单位 "timezone"

postgres=# select extract(timezone_hour from '2011-05-17 12:56:20.1234'::timestamp) AAA;
错误:  不支持时间戳单位 "timezone_hour"
文章标签：
云原生数据库 PolarDB
关系型数据库
SQL
Oracle
PostgreSQL
JavaScript
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
瀚高大李
目录
相关文章
游客vphb4ae2je2zi
|
12月前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB，多种引擎优惠上线！MySQL倚天版88元/年，SQL Server 2核4G仅299元/年，PostgreSQL 227元/年起。高可用、可弹性伸缩，安全稳定。详情见官网活动页。
游客vphb4ae2je2zi
1748 152
上云就上阿狸云
|
12月前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎，提供高性价比、稳定安全的云数据库服务，适用于多种行业与业务场景。
上云就上阿狸云
1161 156
程序员爱钓鱼
|
SQL 关系型数据库 MySQL
Go语言数据库编程：使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口，支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容，涵盖实际开发中常用的技巧与注意事项，适合快速掌握Go语言数据库编程基础。
程序员爱钓鱼
1668 213
上云就上阿狸云
|
12月前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型，包括关系型与NoSQL，主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起，支持按需付费与优惠套餐，适用于各类应用场景。
上云就上阿狸云
1839 4
蓝易云
|
12月前
|
SQL Oracle 关系型数据库
Oracle数据库创建表空间和索引的SQL语法示例
以上SQL语法提供了一种标准方式去组织Oracle数据库内部结构，并且通过合理使用可以显著改善查询速度及整体性能。需要注意，在实际应用过程当中应该根据具体业务需求、系统资源状况以及预期目标去合理规划并调整参数设置以达到最佳效果。
蓝易云
706 8
赵渝强老师
|
Oracle 关系型数据库 数据库
【赵渝强老师】在PostgreSQL中访问Oracle
本文介绍了如何在PostgreSQL中使用oracle_fdw扩展访问Oracle数据库数据。首先需从Oracle官网下载三个Instance Client安装包并解压，设置Oracle环境变量。接着从GitHub下载oracle_fdw扩展，配置pg_config环境变量后编译安装。之后启动PostgreSQL服务器，在数据库中创建oracle_fdw扩展及外部数据库服务，建立用户映射。最后通过创建外部表实现对Oracle数据的访问。文末附有具体操作步骤与示例代码。
赵渝强老师
1464 6
【赵渝强老师】在PostgreSQL中访问Oracle
大熊计算机
|
SQL 关系型数据库 PostgreSQL
CTE vs 子查询：深入拆解PostgreSQL复杂SQL的隐藏性能差异
本文深入探讨了PostgreSQL中CTE（公共表表达式）与子查询的选择对SQL性能的影响。通过分析两者底层机制，揭示CTE的物化特性及子查询的优化融合优势，并结合多场景案例对比执行效率。最终给出决策指南，帮助开发者根据数据量、引用次数和复杂度选择最优方案，同时提供高级优化技巧和版本演进建议，助力SQL性能调优。
大熊计算机
1556 1
三桥君
|
SQL 人工智能 数据挖掘
如何在`score`表中正确使用`COUNT`和`AVG`函数？SQL聚合函数COUNT与AVG使用指南
本文三桥君通过score表实例解析SQL聚合函数COUNT和AVG的常见用法。详解COUNT(studentNo)、COUNT(score)、COUNT()的区别，以及AVG函数对数值/字符型字段的不同处理，特别指出AVG()是无效语法。实战部分提供6个典型查询案例及结果，包含创建表、插入数据的完整SQL代码。产品专家三桥君强调正确理解函数特性（如空值处理、字段类型限制）对数据分析的重要性，帮助开发者避免常见误区，提升查询效率。
三桥君
607 0
云流雨洄
|
SQL Oracle 关系型数据库
解决大小写、保留字与特殊字符问题！Oracle双引号在SQL中的特殊应用
在Oracle数据库开发中，双引号的使用是一个重要但易被忽视的细节。本文全面解析了双引号在SQL中的特殊应用场景，包括解决标识符与保留字冲突、强制保留大小写、支持特殊字符和数字开头标识符等。同时提供了最佳实践建议，帮助开发者规避常见错误，提高代码可维护性和效率。
云流雨洄
759 6
游客kufrkwrbkmpsa
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
游客kufrkwrbkmpsa
347 1

热门文章

最新文章

  • 1
    Druid解析SQL语句，遇到横线用反引号转义，规则不统一。请问如何识别
  • 2
    Confluence 6 SQL Server 问题解决
  • 3
    PL/SQL变长数组
  • 4
    使用sqlt手工创建sql_profile
  • 5
    SQL SERVER 2008的QUOTENAME函数
  • 6
    SQL SERVER 取所有表及注释 和 字段属性
  • 7
    SQL SERVER的分页存储过程
  • 8
    无人参与安装最新版本SQL Server Management Studio (SSMS)
  • 9
    【MySQL数据库】SQL查询语句总结
  • 10
    [备忘]删除SQL Server中无登录名的用户
  • 1
    【赵渝强老师】使用NetManager创建Oracle数据库的监听器
    495
  • 2
    【赵渝强老师】Oracle数据库配置助手：DBCA
    918
  • 3
    Oracle数据库创建表空间和索引的SQL语法示例
    706
  • 4
    【赵渝强老师】Oracle客户端与服务器端连接建立的过程
    481
  • 5
    数据库数据恢复—服务器异常断电导致Oracle数据库报错的数据恢复案例
    634
  • 6
    比较MySQL和Oracle数据库系统，特别是在进行分页查询的方法上的不同
    618
  • 7
    服务器数据恢复—光纤存储上oracle数据库数据恢复案例
    381
  • 8
    【赵渝强老师】Oracle RMAN的目录数据库
    400
  • 9
    Oracle存储过程插入临时表优化与慢查询解决方法
    647
  • 10
    oracle数据恢复—oracle数据库执行错误truncate命令的数据恢复案例
    623

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 推荐镜像

    更多
  • postgresql
  • oracle
    • 下一篇
    一条命令迁移，帮你实现 OpenClaw 与 Hermes Agent 记忆互通！