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

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
简介:

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

1、NULL判断函数

Oracle的NULL判断函数是 nvl(A, B) 和 coalesce 两个函数。nvl(A, B) 是判断如果A不为NULL,则返回A, 否则返回B。参数需要是相同类型的,或者可以自动转换成相同类型的, 否则需要显式转换。而 coalese 参数可以有多个,返回第一个不为NULL的参数。而参数必须为相同类型的 ,不会自动转换。
PostgreSQL中没有nvl函数。但是有coalesce函数。用法和Oracle的一样。可以使用coalesce来转换Oracle的nvl和coalesce。参数需要使用相同类型,或者可以转换成相同类型的。否则需要手动转换。

Oracle NULL判断函数

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           111111     05-8月 -16
         1            31-7月 -16
         2 222222

SQL> select nvl(value1, 'Hello') value1 from o_test;
select nvl(value1, 'Hello') value1 from o_test
                   *
第 1 行出现错误:
ORA-01722: 无效数字

SQL> select nvl(value1, '10000') value1 from o_test;

    VALUE1
----------
     10000
         1
         2

SQL> select nvl(value2, 'Hello') value2 from o_test;

VALUE2
----------
111111
Hello
222222

SQL> select nvl(value3, '2010-1-1') value3 from o_test;
select nvl(value3, '2010-1-1') value3 from o_test
                   *
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配

SQL> select nvl(value3, to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;

VALUE3
--------------
05-8月 -16
31-7月 -16
01-1月 -10

SQL> select coalesce(value1, '10000') value1 from o_test;
select coalesce(value1, '10000') value1 from o_test
                        *
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR

SQL> select coalesce(value1, 10000) value1 from o_test;

    VALUE1
----------
     10000
         1
         2

SQL> select coalesce(value2, '',  'Hello John') value2 from o_test;

VALUE2
----------
111111
Hello John
222222

SQL> select coalesce(value3,'', to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;
select coalesce(value3,'', to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test
                       *
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 DATE, 但却获得 CHAR

SQL> select coalesce(value3, null, to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;

VALUE3
--------------
05-8月 -16
31-7月 -16
01-1月 -10

PostgreSQL NULL判断函数

postgres=# select * from p_test;
 value1 | value2 |       value3
--------+--------+---------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2010-01-01 00:00:00
      2 | 22222  |
(3 行记录)

postgres=# select coalesce(value1, 'Hello') value1  from p_test;
错误:  无效的整数类型输入语法: "Hello"
第1行select coalesce(value1, 'Hello') value1  from p_test;
                             ^
postgres=# select coalesce(value1, '10000') value1  from p_test;
 value1
--------
  10000
      1
      2
(3 行记录)

postgres=# select coalesce(value2, null, 'Hello world') value2  from p_test;
   value2
-------------
 11111
 Hello world
 22222
(3 行记录)

postgres=# select coalesce(value3, null, '2012-10-10') value3  from p_test;
       value3
---------------------
 2010-01-01 00:00:00
 2016-08-05 10:01:32
 2012-10-10 00:00:00
(3 行记录)

postgres=# select coalesce(value3, null, '2012-10-A') value3  from p_test;
错误:  无效的类型 timestamp 输入语法: "2012-10-A"
第1行select coalesce(value3, null, '2012-10-A') value3  from p_te...
                                   ^

2、字符串连接

2.1、字符串连接符( || )

Oracle的字符串连接符(||) 和 PostgreSQL的字符串连接符(||)的用法基本相同,不同的地方是
1、当连接的参数有null的时候,Oracle中,null 的连接效果类似于空字符串(''),而PostgreSQL中, 连接的参数中有null的, 连接结果统一都是null。
2、当几个参数都是数字的时候,Oracle会自动把数字转换为字符串。这个和Oracle内部的自动类型转换有关系。而PostgreSQL中,几个参数中至少有一个应该为字符串,否则会报错。

数据迁移的时候,对于Oracle的 A || B 可以使用PostgreSQL的coalesce( A, '') || coalesce( B, '')形式来转换。

Oracle 字符串连接符( || )
SQL> select 'abc' || 'def' value from dual;

VALUE
------
abcdef

SQL> select 123 || 456 value from dual;

VALUE
------
123456

SQL> select null || 456 value from dual;

VAL
---
456

SQL> select null || 'abcdef'  value from dual;

VALUE
------
abcdef

SQL> select length(null || null) value from dual;

     VALUE
----------

PostgreSQL 字符串连接符( || )
postgres=# select 'abc' || 'def' as value;
 value
--------
 abcdef
(1 行记录)

postgres=# select 123 || 456 as  value;
错误:  操作符不存在: integer || integer
第1行select 123 || 456 as  value;
                ^
提示:  没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
postgres=# select 123||'456' as value;
 value
--------
 123456
(1 行记录)

postgres=#  select null || 456 as  value ;
 value
-------

(1 行记录)

postgres=# select null || 'abcdef'  as value;
 value
-------

(1 行记录)

postgres=# select length(null || null) as value ;
 value
-------

(1 行记录)

2.2、字符串连接函数concat

Oracle的concat函数类似于字符串连接符(||),但只能够连接两个参数。参数需要是字符串类型,或者可以自动转换成字符串类型。
PostgreSQL中也内置了这个方法。
需要注意的是,Oracle的concat,如果两个参数都是null, 则结果是null。而PostgreSQL中,如果两个参数都是null,则 结果是空字符串('')。因为PostgreSQL的concat方法内部对于参数做了coalesce(null, '')处理。

Oracle concat
SQL> select concat('abc','def') from dual;

CONCAT
------
abcdef

SQL> select concat(123, 456) from dual;

CONCAT
------
123456

SQL> select concat(null, 456) value from dual;

VAL
---
456

SQL> select concat(null, 'abc') value from dual;

VAL
---
abc

SQL> select concat(null, null) value from dual;

V
-


SQL> select length(concat(null, null)) value from dual;

     VALUE
----------

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           111111     05-8月 -16
         1            31-7月 -16
         2 222222

SQL> select concat(value3, value2) from o_test;

CONCAT(VALUE3,VALUE2)
------------------------
05-8月 -16111111
31-7月 -16
222222

PostgreSQL 字符串连接函数
postgres=# select concat('abc','def');
 concat
--------
 abcdef
(1 行记录)

postgres=# select concat(123, 456);
 concat
--------
 123456
(1 行记录)

postgres=# select concat(null, 456) as value;
 value
-------
 456
(1 行记录)


postgres=# select concat(null, 'abc') as value;
 value
-------
 abc
(1 行记录)

postgres=# select concat(null, null) as value;
 value
-------

(1 行记录)

postgres=# select length(concat(null, null)) as value;
 value
-------
     0
(1 行记录)

postgres=# select * from p_test;
 value1 | value2 |       value3
--------+--------+---------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2016-08-05 10:01:32
      2 | 22222  |
(3 行记录)

postgres=# select concat(value3, value2)   as value from p_test;
          value
--------------------------
 2010-01-01 00:00:0011111
 2016-08-05 10:01:32
 22222
(3 行记录)

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
8天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
8天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
14天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
4月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程:使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口,支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容,涵盖实际开发中常用的技巧与注意事项,适合快速掌握Go语言数据库编程基础。
286 62
|
4月前
|
SQL 关系型数据库 PostgreSQL
CTE vs 子查询:深入拆解PostgreSQL复杂SQL的隐藏性能差异
本文深入探讨了PostgreSQL中CTE(公共表表达式)与子查询的选择对SQL性能的影响。通过分析两者底层机制,揭示CTE的物化特性及子查询的优化融合优势,并结合多场景案例对比执行效率。最终给出决策指南,帮助开发者根据数据量、引用次数和复杂度选择最优方案,同时提供高级优化技巧和版本演进建议,助力SQL性能调优。
326 1
|
5月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】在PostgreSQL中访问Oracle
本文介绍了如何在PostgreSQL中使用oracle_fdw扩展访问Oracle数据库数据。首先需从Oracle官网下载三个Instance Client安装包并解压,设置Oracle环境变量。接着从GitHub下载oracle_fdw扩展,配置pg_config环境变量后编译安装。之后启动PostgreSQL服务器,在数据库中创建oracle_fdw扩展及外部数据库服务,建立用户映射。最后通过创建外部表实现对Oracle数据的访问。文末附有具体操作步骤与示例代码。
167 6
【赵渝强老师】在PostgreSQL中访问Oracle
|
7月前
|
SQL 存储 Oracle
【YashanDB知识库】Oracle pipelined函数在YashanDB中的改写
【YashanDB知识库】Oracle pipelined函数在YashanDB中的改写
|
8月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
185 2
|
SQL Oracle 算法
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
2174 3

热门文章

最新文章

推荐镜像

更多