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

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之四：特殊字符和符号

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

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

空字符串( '' )

Oracle中，空字符串( '' )很多时候是和null同样处理的。给varchar2和char类型赋值的时候按照null处理。在给日期类型或者数字类型赋值的时候，也是按照null处理。但是在where条件部分，=‘’和 is null 是不同的。

PostgreSQL里面，空字符串( '' )和 null是不同的。完全是不同的处理。转换SQL的时候，一定要注意。

Oracle 空字符串
SQL> create table o_test(value1 number, value2 varchar2(10), value3 date);

表已创建。

SQL> insert into o_test values('', '11111', to_date( '2010-01-01','YYYY-MM-DD'));

已创建 1 行。

SQL> insert into o_test values(1, '', to_date( '2010-01-01','YYYY-MM-DD'));

已创建 1 行。

SQL> insert into o_test values(2, '22222', to_date( '','YYYY-MM-DD'));

已创建 1 行。

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           11111      01-1月 -10
         1            01-1月 -10
         2 22222

SQL> select * from o_test where value1 = '';

未选定行

SQL> select * from o_test where value1 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           11111      01-1月 -10

SQL> select * from o_test where value2 = '';

未选定行

SQL> select * from o_test where value2 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1            01-1月 -10

SQL> select * from o_test where value3 = '';

未选定行

SQL> select * from o_test where value3 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         2 22222
PostgreSQL 空字符串
postgres=# create table p_test(value1 integer, value2 varchar(10), value3 timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into p_test values('', '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
错误:  无效的整数类型输入语法: ""
第1行insert into p_test values('', '11111', to_timestamp('2010-01...
                               ^
postgres=#  insert into p_test values(null, '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(1, '', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(2, '22222', to_timestamp('', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# select * from p_test;
 value1 | value2 |         value3
--------+--------+------------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2010-01-01 00:00:00
      2 | 22222  | 0001-01-01 00:00:00 BC
(3 行记录)

postgres=# select * from p_test where value1 = '';
错误:  无效的整数类型输入语法: ""
第1行select * from p_test where value1 = '';
                                         ^

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

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

postgres=# select * from p_test where value2 is null;
 value1 | value2 | value3
--------+--------+--------
(0 行记录)

postgres=# select * from p_test where value3 is null;
 value1 | value2 | value3
--------+--------+--------
(0 行记录)

postgres=# select * from p_test where to_char(value3, 'YYYY-MM-DD') ='0001-01-01';
 value1 | value2 |         value3
--------+--------+------------------------
      2 | 22222  | 0001-01-01 00:00:00 BC
(1 行记录)

比较运算符

Oracle中，比较运算符之间是可以有空格的。比如【> = 】这样的写法是允许的。而PostgreSQL中，运算符之间不能有空格。

Oracle 比较运算符
SQL> select * from o_test where value1 > = 2;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         2 22222

SQL> select * from o_test where value1 <                         = 3;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1            01-1月 -10
         2 22222
PostgreSQL 比较运算符
postgres=# select * from p_test where value1 > = 2;
错误:  语法错误 在 "=" 或附近的
第1行select * from p_test where value1 > = 2;
                                         ^
postgres=# select * from p_test where value1 >= 2;
 value1 | value2 |         value3
--------+--------+------------------------
      2 | 22222  | 0001-01-01 00:00:00 BC
(1 行记录)

postgres=# select * from p_test where value1 < = 3;
错误:  语法错误 在 "=" 或附近的
第1行select * from p_test where value1 < = 3;
                                         ^
postgres=# select * from p_test where value1 <= 3;
 value1 | value2 |         value3
--------+--------+------------------------
      1 |        | 2010-01-01 00:00:00
      2 | 22222  | 0001-01-01 00:00:00 BC
(2 行记录)

逃逸字符

逃逸的意思是有特殊意义的字符，前面如果加上逃逸字符的话，就不代表它的特殊含义，而代表它的字符本意。
Oracle的SQL中，使用( ' )来逃逸它本身。Oracle没有其它的标准逃逸字符。比如"n"并不代表回车，而是代表它的本意的"n"两个字符。如果输入回车的话，使用chr(10)来表示。在正则表达式等需要使用特殊字符本意的时候，使用关键字(escape)后面定义的字符进行逃逸。

PostgreSQL的SQL，也支持使用( ' )来逃逸它本身。老版的还支持使用反斜杠( )， 但新版已经不使用。Oracle定义了标准的逃逸字串（E'XXX'） 格式。 在正则表达式等需要使用特殊字符本意的时候，默认使用""做逃逸字串。也可以使用关键字(escape)后面定义的字符进行逃逸。

数据迁移的时候，老版的PostgreSQL中，需要对逃逸字串进行特殊处理。比如把"“替换成"\\"。新版已经不再需要。可以直接按照Oracle的方式直接转换。

Oracle 逃逸字符
SQL> insert into o_test values(1, '12e34'6r8', null);
ERROR:
ORA-01756: 引号内的字符串没有正确结束

SQL> insert into o_test values(1, '12e34''6r8', null);

已创建 1 行。

SQL> insert into o_test values(1, '12e34\n6r8', null);

已创建 1 行。

SQL> insert into o_test values(1, '12e34' || chr(10) || '6r8', null);

已创建 1 行。

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678
         1 12e34%6r8
         1 12e34'6r8
         1 12e34\n6r8
         1 12e34
           6r8

SQL> select * from o_test where value2 like '%_%';

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678
         1 12e34%6r8
         1 12e34'6r8
         1 12e34\n6r8
         1 12e34
           6r8

SQL> select * from o_test where value2 like '%\_%';

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 12e34\n6r8

SQL> select * from o_test where value2 like '%\_%' escape '\' ;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678

SQL> select * from o_test where value2 like '%r_%' escape 'r' ;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678
PostgreSQL 逃逸字符
postgres=#  insert into p_test values(1, '12e34'6r8', null);
postgres'# ');
错误:  语法错误 在 "6" 或附近的
第1行insert into p_test values(1, '12e34'6r8', null);
                                         ^
postgres=#  insert into p_test values(1, '12e34''6r8', null);
INSERT 0 1
postgres=#  insert into p_test values(1, '\n\r', null);
INSERT 0 1
postgres=# insert into p_test values(1, E'ab\n\rc', null);
INSERT 0 1
postgres=# insert into p_test values(1, 'ab'|| chr(10) || 'c', null);
INSERT 0 1
postgres=# select * from p_test;
 value1 |  value2   | value3
--------+-----------+--------
      1 | 1234_678  |
      1 | 12e34%6r8 |
      1 | 12e34'6r8 |
      1 | \n\r      |
      1 | ab       +|
        | \rc       |
      1 | ab       +|
        | c         |
(6 行记录)

postgres=# select * from p_test where value2 like '%_%';
 value1 |  value2   | value3
--------+-----------+--------
      1 | 1234_678  |
      1 | 12e34%6r8 |
      1 | 12e34'6r8 |
      1 | \n\r      |
      1 | ab       +|
        | \rc       |
      1 | ab       +|
        | c         |
(6 行记录)

postgres=# select * from p_test where value2 like '%\_%';
 value1 |  value2  | value3
--------+----------+--------
      1 | 1234_678 |
(1 行记录)

postgres=# select * from p_test where value2 like '%r_%' escape 'r' ;
 value1 |  value2  | value3
--------+----------+--------
      1 | 1234_678 |
(1 行记录)

postgres=# select * from p_test where value2 like '%\\%';
 value1 | value2 | value3
--------+--------+--------
      1 | \n\r   |
(1 行记录)
文章标签：
云原生数据库 PolarDB
关系型数据库
SQL
PostgreSQL
Oracle
数据库
存储
开发者
关键词：
SQL差异
SQL postgresql
postgresql SQL
Oracle数据库一键上云sql
SQL字符
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
瀚高大李
目录
相关文章
蓝易云
|
4月前
|
SQL Oracle 关系型数据库
Oracle数据库创建表空间和索引的SQL语法示例
以上SQL语法提供了一种标准方式去组织Oracle数据库内部结构，并且通过合理使用可以显著改善查询速度及整体性能。需要注意，在实际应用过程当中应该根据具体业务需求、系统资源状况以及预期目标去合理规划并调整参数设置以达到最佳效果。
蓝易云
384 8
赵渝强老师
|
8月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】在PostgreSQL中访问Oracle
本文介绍了如何在PostgreSQL中使用oracle_fdw扩展访问Oracle数据库数据。首先需从Oracle官网下载三个Instance Client安装包并解压，设置Oracle环境变量。接着从GitHub下载oracle_fdw扩展，配置pg_config环境变量后编译安装。之后启动PostgreSQL服务器，在数据库中创建oracle_fdw扩展及外部数据库服务，建立用户映射。最后通过创建外部表实现对Oracle数据的访问。文末附有具体操作步骤与示例代码。
赵渝强老师
464 6
【赵渝强老师】在PostgreSQL中访问Oracle
云流雨洄
|
9月前
|
SQL Oracle 关系型数据库
解决大小写、保留字与特殊字符问题！Oracle双引号在SQL中的特殊应用
在Oracle数据库开发中，双引号的使用是一个重要但易被忽视的细节。本文全面解析了双引号在SQL中的特殊应用场景，包括解决标识符与保留字冲突、强制保留大小写、支持特殊字符和数字开头标识符等。同时提供了最佳实践建议，帮助开发者规避常见错误，提高代码可维护性和效率。
云流雨洄
383 6
游客kufrkwrbkmpsa
|
10月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
游客kufrkwrbkmpsa
208 1
游客kufrkwrbkmpsa
|
10月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
本文来自YashanDB官网，介绍如何处理Oracle客户端sql*plus中使用@@调用同级目录SQL脚本的场景。崖山数据库23.2.x.100已支持@@用法，但旧版本可通过Python脚本批量重写SQL文件，将@@替换为绝对路径。文章通过Oracle示例展示了具体用法，并提供Python脚本实现自动化处理，最后调整批处理脚本以适配YashanDB运行环境。
游客kufrkwrbkmpsa
219 0
赵渝强老师
|
4月前
|
Oracle 关系型数据库 Linux
【赵渝强老师】Oracle数据库配置助手：DBCA
Oracle数据库配置助手（DBCA）是用于创建和配置Oracle数据库的工具，支持图形界面和静默执行模式。本文介绍了使用DBCA在Linux环境下创建数据库的完整步骤，包括选择数据库操作类型、配置存储与网络选项、设置管理密码等，并提供了界面截图与视频讲解，帮助用户快速掌握数据库创建流程。
赵渝强老师
460 93
赵渝强老师
|
3月前
|
Oracle 关系型数据库 Linux
【赵渝强老师】使用NetManager创建Oracle数据库的监听器
Oracle NetManager是数据库网络配置工具，用于创建监听器、配置服务命名与网络连接，支持多数据库共享监听，确保客户端与服务器通信顺畅。
赵渝强老师
269 0
1436047922066202
|
6月前
|
存储 Oracle 关系型数据库
服务器数据恢复—光纤存储上oracle数据库数据恢复案例
一台光纤服务器存储上有16块FC硬盘，上层部署了Oracle数据库。服务器存储前面板2个硬盘指示灯显示异常，存储映射到linux操作系统上的卷挂载不上，业务中断。 通过storage manager查看存储状态，发现逻辑卷状态失败。再查看物理磁盘状态，发现其中一块盘报告“警告”，硬盘指示灯显示异常的2块盘报告“失败”。 将当前存储的完整日志状态备份下来，解析备份出来的存储日志并获得了关于逻辑卷结构的部分信息。
1436047922066202
193 4
赵渝强老师
|
6月前
|
存储 Oracle 关系型数据库
【赵渝强老师】Oracle RMAN的目录数据库
Oracle RMAN默认将备份元信息存储在控制文件中，但控制文件损坏或丢失会导致恢复失败，且备份增多会使控制文件无限增长。为解决这些问题，Oracle引入了RMAN目录数据库（Catalog Database），专门用于存储RMAN备份的元信息。使用目录数据库可提升备份管理效率，支持多数据库共享、长期备份历史记录存储，并可保存RMAN脚本。本文详细介绍了如何创建目录数据库、注册目标数据库及其操作步骤。
赵渝强老师
187 0
蓝易云
|
9月前
|
Oracle 安全 关系型数据库
【Oracle】使用Navicat Premium连接Oracle数据库两种方法
以上就是两种使用Navicat Premium连接Oracle数据库的方法介绍，希望对你有所帮助！
蓝易云
2016 28

热门文章

最新文章

  • 1
    如何搭建阿里云RDS PostgreSQL数据库的物理备库
  • 2
    PostgreSQL Logical Replication
  • 3
    阿里云AliCloudDB PostgreSQL 分区表功能性能比社区版提升100倍
  • 4
    专访阿里云工程师范孝剑：ApsaraDB for PostgreSQL将继续主打特色解决方案
  • 5
    PostgreSQL 物联网黑科技 - 瘦身500倍的索引(范围索引)
  • 6
    PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box)
  • 7
    PostgreSQL常用命令，启动连接，pg_dump导入导出
  • 8
    Locking issue with concurrent DELETE / INSERT in PostgreSQL - 解法 advisory lock
  • 9
    拯救海量数据：PostgreSQL分区表性能优化实战手册（附压测对比）
  • 10
    PostgreSQL基础之psql的使用
  • 1
    Web LLM 实验：利用 LLM API 实现 SQL 注入
    437
  • 2
    SQL 注入神器：SQLMap 简单使用
    443
  • 3
    奇思妙想的SQL｜兼顾性能的数据倾斜处理新姿势
    542
  • 4
    如何巧用索引优化SQL语句性能？
    322
  • 5
    SQL INNER JOIN 关键字
    121
  • 6
    SQL 连接(JOIN)
    185
  • 7
    SQL DB - 关系型数据库是如何工作的
    160
  • 8
    利用SparkSQL Logical Plan Parse 打造大数据平台SQL诊断利器
    350
  • 9
    SQL BETWEEN 操作符
    162
  • 10
    SQL IN 操作符
    118

    • 相关课程

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

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 推荐镜像

    更多
  • oracle
    • 下一篇
    附部署代码｜云数据库RDS 全托管 Supabase服务：小白轻松搞定开发AI应用