关于postgresql正则表达式的信息

以下这个postgreSQL是什么意思

这段代码主体是case when 判断语句，是根据i.time_at_job值的不同而返回不同的数值。

前面3个when是按特定的字串来返回特定的数值(如6_m返回0; 6_months_to_1_year返回1; 1_year_to_2_years返回2)；

从第四个when开始(有~ '^[0-9]+$'的条件)，是先按照正则表达式匹配到i.time_at_job为数值字符串，再按照将i.time_at_job转换为数值之后的范围来返回不同的值: 大于等于5且小于12，或者大于等于60返回5；大于等于12时返回除以12的四舍五入值整数(round函数)；如果小于5，则返回转换后的整数值。

~ '^[0-9]+$'这个符号应该作为两个部分: ~为正则表达式的匹配运算符，它是区分大小写的，它是一个2元运算符，如果~前的字符串匹配后面的正则表达式，则返回true，否则返回false；后面的字符串为一个正则表达式字符串，代表要匹配的字符串必须全部为数字，并且至少有一个数字。用在该SQL中的作用就是确保i.time_at_job的值为一个数字字符串。

PostgreSQL模式匹配关键字similar to 和 like 的区别

postgre官方文档中对similar to 和 like的说明：

string LIKE pattern [ ESCAPE escape-character ]

每个 pattern 定义一个字串的集合。 如果该 string 包含在 pattern 代表的字串集合里，那么 LIKE 表达式返回真。如果 pattern 不包含百分号或者下划线，那么该模式只代表它本身，这时候 LIKE 的行为就象等号操作符。在 pattern 里的下划线（_）代表（匹配）任何单个字符；而一个百分号（%）匹配任何零或更多字符长的字串。

例如：

'abc' LIKE 'abc' true

'abc' LIKE 'a%' true

'abc' LIKE '_b_' true

'abc' LIKE 'c' false

string SIMILAR TO pattern [ESCAPE escape-character]

SIMILARTO 根据自己的模式是否匹配给定字串而返回真或者假。它和 LIKE 非常类似，只不过它使用SQL标准定义的正则表达式理解模式。SQL 标准的正则表达式是在LIKE表示法和普通的正则表达式表示法之间古怪的交叉。类似 LIKE，SIMILAR TO 操作符只有在它的模式匹配整个字串的时候才能成功；这一点和普通的正则表达式的习惯不同，在普通的正则表达式里，模式匹配字串的任意部分。和 LIKE 类似的地方还有，SIMILAR TO 使用 _ 和 % 作为分别代表任意字串和任意字串字符的通配符。（这些和 POSIX 正则表达式里的 . 和 .* 兼容）除了这些从 LIKE 借用的功能之外， SIMILAR TO 支持下面这些从 POSIX 正则表达式借用的模式匹配元字符：

· | 标识选择（两个候选之一）。

· * 表示重复前面的项零次或更多次。

· + 表示重复前面的项一次或更多次。

· 可以使用圆括弧 () 把项组合成一个逻辑项。

· 一个方括弧表达式 [...] 声明一个字符表， 就像 POSIX 正则表达式一样。

例如：

'abc' SIMILAR TO 'abc' true

'abc' SIMILAR TO 'a' false

'abc' SIMILAR TO '%(b|d)%' true

'abc' SIMILAR TO '(b|c)%' false

如何解决postgresql 错误：08p01：无效的信息格式

Appendix A. PostgreSQL错误代码

PostgreSQL服务器发出的所有消息都赋予 了五个字符的错误代码，这些代码遵循 SQL 的"SQLSTATE" 代码的习惯。需要知道发生了什么错误条件的应用通常应该测试错误代码， 而不是查看文本错误信息。这些错误代码轻易不会随着PostgreSQL 的版本更新而修改，并且一般也不会随着错误信息的本地化而发生修改。 请注意有些(但不是全部)PostgreSQL生成的错误代码是 由 SQL 标准定义的；有些标准没有定义的错误条件是发明的或者是从其它数据库借来的。

根据标准，错误代码的头两个字符表示错误类别， 而后三个字符表示在该类别内特定的条件。因此， 那些不能识别特定错误代码的应用仍然可以从错误类别中推断要做什么。

Table A-1里面列出了PostgreSQL 8.2.3 定义的所有错误代码(有些实际上目前并没有使用， 但是 SQL 标准定义了)。错误类别也列出在此。对于每个错误类别都有个 "标准"的错误代码，它的最后三个字符是000。 这个代码只用于那些落在该类别内，但是没有赋予任何更准确的代码的错误条件。

PL/pgSQL用于每个错误代码的条件名和表中显示的措辞相同， 只是用下划线代替了空白。比如，代码22012, DIVISION BY ZERO 的条件名是DIVISION_BY_ZERO。 条件名大小写无关。(请注意PL/pgSQL并不识别警告，这一点和错误、条件名正相反；那些是 00, 01, 02 类别。)

Table A-1. PostgreSQL 错误代码

错误代码

含义

常量名

Class 00 — 成功完成

00000 成功完成 successful_completion

Class 01 — 警告

01000 警告 warning

0100C 返回了动态结果 dynamic_result_sets_returned

01008 警告，隐含补齐了零比特位 implicit_zero_bit_padding

01003 在集合函数里消除null null_value_eliminated_in_set_function

01007 没有赋予权限 privilege_not_granted

01006 没有撤销权限 privilege_not_revoked

01004 字符串数据在右端截断 string_data_right_truncation

01P01 废弃的特性 deprecated_feature

Class 02 — 没有数据(按照 SQL 标准的要求，这也是警告类)

02000 没有数据 no_data

02001 返回了没有附加动态结果集 no_additional_dynamic_result_sets_returned

Class 03 — SQL语句尚未结束

03000 SQL语句尚未结束 sql_statement_not_yet_complete

Class 08 — 连接异常

08000 连接异常 connection_exception

08003 连接不存在 connection_does_not_exist

08006 连接失败 connection_failure

08001 SQL 客户端不能建立 SQL 连接 sqlclient_unable_to_establish_sqlconnection

08004 SQL 服务器拒绝建立 SQL 连接 sqlserver_rejected_establishment_of_sqlconnection

08007 未知的事务解析 transaction_resolution_unknown

08P01 违反协议 protocol_violation

Class 09 — Triggered Action Exception触发器动作异常

09000 触发器动作异常 triggered_action_exception

Class 0A — 不支持特性

0A000 不支持此特性 feature_not_supported

Class 0B — 非法事务初始化

0B000 非法事务初始化 invalid_transaction_initiation

Class 0F — 定位器异常

0F000 定位器异常 locator_exception

0F001 非法的定位器声明 invalid_locator_specification

Class 0L — 非法赋权者

0L000 非法赋权者 invalid_grantor

0LP01 非法赋权操作 invalid_grant_operation

Class 0P — 非法角色声明

0P000 非法角色声明 invalid_role_specification

Class 20 — 未发现情况

20000 未发现情况 case_not_found

Class 21 — 势违例

21000 势违例 cardinality_violation

Class 22 — 数据异常

22000 数据异常 data_exception

2202E 数组下标错误 array_subscript_error

22021 字符不在规定范围内 character_not_in_repertoire

22008 日期时间字段溢出 datetime_field_overflow

22012 被零除 division_by_zero

22005 赋值中出错 error_in_assignment

2200B 逃逸字符冲突 escape_character_conflict

22022 INDICATOR OVERFLOW指示器溢出 indicator_overflow

22015 内部字段溢出 interval_field_overflow

2201E 对数运算的非法参数 invalid_argument_for_logarithm

22014 NTILE函数的无效参数 invalid_argument_for_ntile_function

22016 N倍函数的无效参数 invalid_argument_for_nth_value_function

2201F 指数函数的无效参数 invalid_argument_for_power_function

2201G BUCKET函数的非法参数 invalid_argument_for_width_bucket_function

22018 类型转换时非法的字符值 invalid_character_value_for_cast

22007 非法日期时间格式 invalid_datetime_format

22019 非法的逃逸字符 invalid_escape_character

2200D 非法的逃逸字节 invalid_escape_octet

22025 非法逃逸序列 invalid_escape_sequence

22P06 非标准使用逃逸字符 nonstandard_use_of_escape_character

22010 非法指示器参数值 invalid_indicator_parameter_value

22023 非法参数值 invalid_parameter_value

2201B 非法正则表达式 invalid_regular_expression

2201W 无效的行数限制 invalid_row_count_in_limit_clause

2201X 在结果抵消子句中无效的行数 invalid_row_count_in_result_offset_clause

22009 非法时区显示值 invalid_time_zone_displacement_value

2200C 逃逸字符的非法使用 invalid_use_of_escape_character

2200G 最相关类型不匹配 most_specific_type_mismatch

22004 不允许 NULL 值 null_value_not_allowed

22002 NULL 值不能做指示器参数 null_value_no_indicator_parameter

22003 数字值超出范围 numeric_value_out_of_range

22026 字符串数据长度不匹配 string_data_length_mismatch

22001 字符串数据右边被截断 string_data_right_truncation

22011 抽取子字符串错误 substring_error

22027 截断错误 trim_error

22024 未结束的 C 字符串 unterminated_c_string

2200F 零长度的字符串 zero_length_character_string

22P01 浮点异常 floating_point_exception

22P02 非法文本表现形式 invalid_text_representation

22P03 非法二进制表现形式 invalid_binary_representation

22P04 错误的COPY文件格式 bad_copy_file_format

22P05 不可翻译字符 untranslatable_character

2200L 不是一个XML文档 not_an_xml_document

2200M 无效的XML文档 invalid_xml_document

2200N 无效的XML内容 invalid_xml_content

2200S 无效的XML评论 invalid_xml_comment

2200T 无效的XML处理指令 invalid_xml_processing_instruction

Class 23 — 违反完整性约束

23000 违反完整性约束 integrity_constraint_violation

23001 约束限制 restrict_violation

23502 NOT NULL VIOLATION违反非空 not_null_violation

23503 违反外键约束 foreign_key_violation

23505 违反唯一约束 unique_violation

23514 违反检查 check_violation

23P01 违反排除 exclusion_violation

Class 24 — 非法游标状态

24000 非法游标状态 invalid_cursor_state

Class 25 — 非法事务状态

25000 非法事务状态 invalid_transaction_state

25001 活跃的SQL状态 active_sql_transaction

25002 分支事务已经激活 branch_transaction_already_active

25008 持有的指针要求同样的隔离级别 held_cursor_requires_same_isolation_level

25003 对分支事务的不恰当的访问方式 inappropriate_access_mode_for_branch_transaction

25004 对分支事务的不恰当的隔离级别 inappropriate_isolation_level_for_branch_transaction

25005 分支事务没有活跃的SQL事务 no_active_sql_transaction_for_branch_transaction

25006 只读的SQL事务 read_only_sql_transaction

25007 不支持混和的模式和数据语句 schema_and_data_statement_mixing_not_supported

25P01 没有活跃的SQL事务 no_active_sql_transaction

25P02 在失败的SQL事务中 in_failed_sql_transaction

Class 26 — 非法SQL语句名

26000 非法SQL语句名 invalid_sql_statement_name

Class 27 — 触发数据更改违规

27000 触发数据更改违规 triggered_data_change_violation

Class 28 — 非法授权声明

28000 非法授权声明 invalid_authorization_specification

28P01 非法密码 invalid_password

Class 2B — 依然存在依赖的优先级描述符

2B000 依然存在依赖的优先级描述符 dependent_privilege_descriptors_still_exist

2BP01 依赖性对象仍然存在 dependent_objects_still_exist

Class 2D — 非法的事务终止

2D000 非法的事务终止 invalid_transaction_termination

Class 2F — SQL过程异常

2F000 SQL过程异常 sql_routine_exception

2F005 执行的函数没有返回语句 function_executed_no_return_statement

2F002 不允许修改SQL数据 modifying_sql_data_not_permitted

2F003 企图使用禁止的SQL语句 prohibited_sql_statement_attempted

2F004 不允许读取SQL数据 reading_sql_data_not_permitted

Class 34 — 非法指针名

34000 非法指针名 invalid_cursor_name

Class 38 — 外部过程异常

38000 外部过程异常 external_routine_exception

38001 不允许包含的SQL containing_sql_not_permitted

38002 不允许修改SQL数据 modifying_sql_data_not_permitted

38003 企图使用禁止的SQL语句 prohibited_sql_statement_attempted

38004 不允许读取SQL数据 reading_sql_data_not_permitted

Class 39 — 外部过程调用异常

39000 外部过程调用异常 external_routine_invocation_exception

39001 返回了非法的SQL状态 invalid_sqlstate_returned

39004 不允许使用NULL null_value_not_allowed

39P01 违反触发器协议 trigger_protocol_violated

39P02 违反 SRF 协议 srf_protocol_violated

Class 3B — 保存点异常

3B000 保存点异常 savepoint_exception

3B001 无效的保存点声明 invalid_savepoint_specification

Class 3D — 非法目录名

3D000 非法目录名 invalid_catalog_name

Class 3F — 非法模式名

3F000 非法模式名 invalid_schema_name

Class 40 — 事务回滚

40000 事务回滚 transaction_rollback

40002 违反事务完整性约束 transaction_integrity_constraint_violation

40001 串行化失败 serialization_failure

40003 不知道语句是否结束 statement_completion_unknown

40P01 侦测到死锁 deadlock_detected

Class 42 — 语法错误或者违反访问规则

42000 语法错误或者违反访问规则 syntax_error_or_access_rule_violation

42601 语法错误 syntax_error

42501 权限不够 insufficient_privilege

42846 无法进行类型转换 cannot_coerce

42803 分组错误 grouping_error

42P20 开窗口错误 windowing_error

42P19 非法递归 invalid_recursion

42830 非法的外键 invalid_foreign_key

42602 非法名称 invalid_name

42622 名称过长 name_too_long

42939 保留名称 reserved_name

42804 数据类型不匹配 datatype_mismatch

42P18 模糊数据类型 indeterminate_datatype

42809 错误的对象类型 wrong_object_type

42703 未定义的字段 undefined_column

42883 未定义的函数 undefined_function

42P01 未定义的表 undefined_table

42P02 未定义的参数 undefined_parameter

42704 未定义对象 undefined_object

42701 重复的字段 duplicate_column

42P03 重复的游标 duplicate_cursor

42P04 重复的数据库 duplicate_database

42723 重复的函数 duplicate_function

42P05 重复的预备语句 duplicate_prepared_statement

42P06 重复的模式 duplicate_schema

42P07 重复的表 duplicate_table

42712 重复的别名 duplicate_alias

42710 重复的对象 duplicate_object

42702 模糊的字段 ambiguous_column

42725 模糊的函数 ambiguous_function

42P08 模糊的参数 ambiguous_parameter

42P09 模糊的别名 ambiguous_alias

42P10 非法字段引用 invalid_column_reference

42611 非法字段定义 invalid_column_definition

42P11 非法游标定义 invalid_cursor_definition

42P12 非法数据库定义 invalid_database_definition

42P13 非法函数定义 invalid_function_definition

42P14 非法预备语句定义 invalid_prepared_statement_definition

42P15 非法模式定义 invalid_schema_definition

42P16 非法表定义 invalid_table_definition

42P17 非法对象定义 invalid_object_definition

Class 44 — 违反 WITH CHECK 选项

44000 违反 WITH CHECK 选项 with_check_option_violation

postgreSQL sql语句中的~~符号是什么意思

操作符 ~~ 等效于 LIKE， 而 ~~* 对应 ILIKE。 还有 !~~ 和 !~~* 操作符 分别代表 NOT LIKE 和 NOT ILIKE。

另外：

~ 匹配正则表达式，大小写相关 'thomas' ~ '.*thomas.*'

~* 匹配正则表达式，大小写无关 'thomas' ~* '.*Thomas.*'

!~ 不匹配正则表达式，大小写相关 'thomas' !~ '.*Thomas.*'

!~* 不匹配正则表达式，大小写无关 'thomas' !~* '.*vadim.*'

看大数据最大技术难关之模糊检索，PostgreSQL如何攻克

大数据正在向我们奔来。尽管业务场景不会完全相同，但在其中一个最典型场景——模糊检索中，技术需求却出奇的一致。

比如说：

物联网，往往会产生大量的数据，除了数字数据，还有字符串类的数据，例如条形码，车牌，手机号，邮箱，姓名等。假设用户需要在大量的传感数据中进行模糊检索，甚至规则表达式匹配，有什么高效的方法呢？

医药，市面上发现了一批药品可能有问题，需要对药品条码进行规则表达式查找，找出复合条件的药品流向。但怎么才能在如此复杂的系统中，用高效方法来实现？

公安，侦查行动时，有可能需要线索的检索。如用户提供的残缺的电话号码，邮箱，车牌，IP地址，QQ号码，微信号码等进行交叉搜索，根据这些信息加上时间的叠加，模糊匹配和关联，最终找出罪犯。但这个流程，可有高效方法？

相同的需求还有很多。几乎每一个模糊匹配的场景下，都需要正则表达式匹配，这和人脸拼图有点类似，我们已经看到强烈的需求已经产生。但技术方面，要怎么做更好？

在我看来：正则匹配和模糊匹配通常是搜索引擎的特长，但是如果你使用的是PostgreSQL数据库照样能实现，并且性能不赖，加上分布式方案

(譬如 plproxy, pg_shard, fdw shard, pg-xc, pg-xl,

greenplum)，处理百亿以上数据量的正则匹配和模糊匹配效果杠杠的，同时还不失数据库固有的功能，绝对是一举多得。

首先对应用场景进行一下分类，以及现有技术下能使用的优化手段。

.1. 带前缀的模糊查询，例如 like 'ABC%'，在PG中也可以写成 ~ '^ABC'

可以使用btree索引优化，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

.2. 带后缀的模糊查询，例如 like '%ABC'，在PG中也可以写成 ~ 'ABC$'

可以使用reverse函数btree索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

.3. 不带前缀和后缀的模糊查询，例如 like '%AB_C%'，在PG中也可以写成 ~ 'AB.C'

可以使用pg_trgm的gin索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

.4. 正则表达式查询，例如 ~ '[\d]+def1.?[a|b|0|8]{1,3}'

可以使用pg_trgm的gin索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

PostgreSQL pg_trgm插件自从9.1开始支持模糊查询使用索引，从9.3开始支持规则表达式查询使用索引，大大提高了PostgreSQL在刑侦方面的能力。

代码见

pg_trgm插件的原理，将字符串前加2个空格，后加1个空格，组成一个新的字符串，并将这个新的字符串按照每3个相邻的字符拆分成多个token。

当使用规则表达式或者模糊查询进行匹配时，会检索出他们的近似度，再进行filter。

postgresql like 特殊字符怎么处理

PostgreSQL 提供了三种实现模式匹配的方法：传统 SQL 的 LIKE 操作符、SQL99 新增的 SIMILAR TO 操作符、 POSIX 风格的正则表达式。另外还有一个模式匹配函数 substring可用，它可以使用 SIMILAR TO 风格或者 POSIX 风格的正则表达式

LIKE

string LIKE pattern [ESCAPE escape-character]

string NOT LIKE pattern [ESCAPE escape-character]

每个 pattern 定义一个字符串的集合。如果该 string 包含在 pattern 代表的字符串集合里，那么 LIKE 表达式返回真。和我们想像的一样，如果 LIKE 返回真，那么 NOT LIKE 表达式将返回假，反之亦然。一个等效的表达式是 NOT (string LIKE pattern) 。

如果 pattern 不包含百分号或者下划线，那么该模式只代表它本身；这时候 LIKE 的行为就像等号操作符。在 pattern 里的下划线(_)匹配任何单个字符；而一个百分号(%)匹配零或多个任何字符。

一些例子：

'abc' LIKE 'abc' true

'abc' LIKE 'a%' true

'abc' LIKE '_b_' true

'abc' LIKE 'c' false

LIKE 模式匹配总是覆盖整个字符串。要匹配在字符串内部任何位置的序列，该模式必须以百分号开头和结尾。

要匹配下划线或者百分号本身，在 pattern 里相应的字符必须前导逃逸字符。缺省的逃逸字符是反斜杠，但是你可以用 ESCAPE 子句指定一个。要匹配逃逸字符本身，写两个逃逸字符。

请注意反斜杠在字符串文本里已经有特殊含义了，所以如果你写一个包含反斜杠的模式常量，那你就要在 SQL 语句里写两个反斜杠。因此，写一个匹配单个反斜杠的模式实际上要在语句里写四个反斜杠。你可以通过用 ESCAPE 选择一个不同的逃逸字符来避免这样；这样反斜杠就不再是 LIKE 的特殊字符了。但仍然是字符文本分析器的特殊字符，所以你还是需要两个反斜杠。

我们也可以通过写成 ESCAPE '' 的方式关闭逃逸机制，这时，我们就不能关闭下划线和百分号的特殊含义。

关键字 ILIKE 可以用于替换 LIKE ，令该匹配就当前的区域设置是大小写无关的。这个特性不是 SQL 标准，是 PostgreSQL 扩展。

操作符 ~~ 等效于 LIKE ，而 ~~* 等效于 ILIKE 。还有 !~~ 和 !~~* 操作符分别代表 NOT LIKE 和 NOT ILIKE 。所有这些操作符都是 PostgreSQL 特有的.

例子: 查找数据库中表的名字(查找以tbl_开头的表的名字)

select tablename from pg_tables where tablename like 'tbl!_%'ESCAPE '!';

利用escape转义: 上面的sql语句中就是将!作为转义字符,作用是将"_"转换成普通的"_"

postgresql正则表达式的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于、postgresql正则表达式的信息别忘了在本站进行查找喔。