PostgreSQL教程(五)：函数和操作符详解（1）
一、逻辑操作符：
常用的逻辑操作符有：AND、OR和NOT。其语义与其它编程语言中的逻辑操作符完全相同。
二、比较操作符：
下面是PostgreSQL中提供的比较操作符列表：
比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符，且返回boolean类型。除了比较操作符以外，我们还可以使用BETWEEN语句，如：
a BETWEEN x AND y 等效于 a >= x AND a <= y
a NOT BETWEEN x AND y 等效于 a < x OR a > y
三、 数学函数和操作符：
下面是PostgreSQL中提供的数学操作符列表：
按位操作符只能用于整数类型，而其它的操作符可以用于全部数值数据类型。按位操作符还可以用于位串类型bit和bit varying，
下面是PostgreSQL中提供的数学函数列表，需要说明的是，这些函数中有许多都存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。
三角函数列表：
四、字符串函数和操作符：
下面是PostgreSQL中提供的字符串操作符列表：
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
| string || string | text | 字串连接 | 'Post' || 'greSQL' | PostgreSQL |
| bit_length(string) | int | 字串里二进制位的个数 | bit_length('jose') | 32 |
| char_length(string) | int | 字串中的字符个数 | char_length('jose') | 4 |
| convert(string using conversion_name) | text | 使用指定的转换名字改变编码。 | convert('PostgreSQL' using iso_8859_1_to_utf8) | 'PostgreSQL' |
| lower(string) | text | 把字串转化为小写 | lower('TOM') | tom |
| octet_length(string) | int | 字串中的字节数 | octet_length('jose') | 4 |
| overlay(string placing string from int [for int]) | text | 替换子字串 | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
| position(substring in string) | int | 指定的子字串的位置 | position('om' in 'Thomas') | 3 |
| substring(string [from int] [for int]) | text | 抽取子字串 | substring('Thomas' from 2 for 3) | hom |
| substring(string from pattern) | text | 抽取匹配 POSIX 正则表达式的子字串 | substring('Thomas' from '...$') | mas |
| substring(string from pattern for escape) | text | 抽取匹配SQL正则表达式的子字串 | substring('Thomas' from '%#"o_a#"_' for '#') | oma |
| trim([leading | trailing | both] [characters] from string) | text | 从字串string的开头/结尾/两边/ 删除只包含characters(缺省是一个空白)的最长的字串 | trim(both 'x' from 'xTomxx') | Tom |
| upper(string) | text | 把字串转化为大写。 | upper('tom') | TOM |
| ascii(text) | int | 参数第一个字符的ASCII码 | ascii('x') | 120 |
| btrim(string text [, characters text]) | text | 从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串 | btrim('xyxtrimyyx','xy') | trim |
| chr(int) | text | 给出ASCII码的字符 | chr(65) | A |
| convert(string text, [src_encoding name,] dest_encoding name) | text | 把字串转换为dest_encoding | convert( 'text_in_utf8', 'UTF8', 'LATIN1') | 以ISO 8859-1编码表示的text_in_utf8 |
| initcap(text) | text | 把每个单词的第一个子母转为大写，其它的保留小写。单词是一系列字母数字组成的字符，用非字母数字分隔。 | initcap('hi thomas') | Hi Thomas |
| length(string text) | int | string中字符的数目 | length('jose') | 4 |
| lpad(string text, length int [, fill text]) | text | 通过填充字符fill(缺省时为空白)，把string填充为长度length。 如果string已经比length长则将其截断(在右边)。 | lpad('hi', 5, 'xy') | xyxhi |
| ltrim(string text [, characters text]) | text | 从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。 | ltrim('zzzytrim','xyz') | trim |
| md5(string text) | text | 计算给出string的MD5散列，以十六进制返回结果。 | md5('abc') | |
| repeat(string text, number int) | text | 重复string number次。 | repeat('Pg', 4) | PgPgPgPg |
| replace(string text, from text, to text) | text | 把字串string里出现地所有子字串from替换成子字串to。 | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
| rpad(string text, length int [, fill text]) | text | 通过填充字符fill(缺省时为空白)，把string填充为长度length。如果string已经比length长则将其截断。 | rpad('hi', 5, 'xy') | hixyx |
| rtrim(string text [, character text]) | text | 从字串string的结尾删除只包含character(缺省是个空白)的最长的字 | rtrim('trimxxxx','x') | trim |
| split_part(string text, delimiter text, field int) | text | 根据delimiter分隔string返回生成的第field个子字串(1 Base)。 | split_part('abc~@~def~@~ghi', '~@~', 2) | def |
| strpos(string, substring) | text | 声明的子字串的位置。 | strpos('high','ig') | 2 |
| substr(string, from [, count]) | text | 抽取子字串。 | substr('alphabet', 3, 2) | ph |
| to_ascii(text [, encoding]) | text | 把text从其它编码转换为ASCII。 | to_ascii('Karel') | Karel |
| to_hex(number int/bigint) | text | 把number转换成其对应地十六进制表现形式。 | to_hex(9223372036854775807) | 7fffffffffffffff |
| translate(string text, from text, to text) | text | 把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。 | translate('12345', '14', 'ax') | a23x5 |
五、位串函数和操作符：
对于类型bit和bit varying，除了常用的比较操作符之外，还可以使用以下列表中由PostgreSQL提供的位串函数和操作符，其中&、|和#的位串操作数必须等长。在移位的时候，保留原始的位串的的长度。
除了以上列表中提及的操作符之外，位串还可以使用字符串函数：length， bit_length， octet_length， position， substring。此外，我们还可以在整数和bit之间来回转换，如：
MyTest=# SELECT 44::bit(10);
bit
------------
0000101100
(1 row)
MyTest=# SELECT 44::bit(3);
bit
-----
100
(1 row)
MyTest=# SELECT cast(-44 as bit(12));
bit
--------------
111111010100
(1 row)
MyTest=# SELECT '1110'::bit(4)::integer;
int4
------
14
(1 row)
注意：如果只是转换为"bit"，意思是转换成bit(1)，因此只会转换成整数的最低位。
该博客中提供的所有信息均源自PostgreSQL官方文档，编写该篇博客的主要目的是便于今后的查阅，特此声明。
相关推荐
-
PostgreSQL教程(三)：表的继承和分区表详解
一.表的继承: 这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生,然而它的实现方式和设计原理却是简单易懂,现在就让我们从一个简单的例子开始吧. 1. 第一个继承表: 复制代码 代码如下: CREATE TABLE cities ( --父表 name text, population float, altitude int ); CREATE TABLE capitals (
-
PostgreSQL教程(十一)：服务器配置
一.服务器进程的启动和关闭: 下面是pg_ctl命令的使用方法和常用选项,需要指出的是,该命令是postgres命令的封装体,因此在使用上比直接使用postgres更加方便. 复制代码 代码如下: pg_ctl init[db] [-D DATADIR] [-s] [-o "OPTIONS"] pg_ctl start [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
-
PostgreSQL教程(七)：函数和操作符详解（3）
九.序列操作函数: 序列对象(也叫序列生成器)都是用CREATE SEQUENCE创建的特殊的单行表.一个序列对象通常用于为行或者表生成唯一的标识符.下面序列函数,为我们从序列对象中获取最新的序列值提供了简单和并发读取安全的方法. 函数 返回类型 描述 nextval(regclass) bigint 递增序列对象到它的下一个数值并且返回该值.这个动作是自动完成的.即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值. currval(regclass) bigint 在当
-
PostgreSQL教程(十二)：角色和权限管理介绍
PostgreSQL是通过角色来管理数据库访问权限的,我们可以将一个角色看成是一个数据库用户,或者一组数据库用户.角色可以拥有数据库对象,如表.索引,也可以把这些对象上的权限赋予其它角色,以控制哪些用户对哪些对象拥有哪些权限. 一.数据库角色: 1. 创建角色: 复制代码 代码如下: CREATE ROLE role_name; 2. 删除角色: 复制代码 代码如下: DROP ROLE role_name; 3. 查询角色: 检查系统表pg_role,如: 复制代码 代码如
-
PostgreSQL教程(十)：性能提升技巧
一.使用EXPLAIN: PostgreSQL为每个查询都生成一个查询规划,因为选择正确的查询路径对性能的影响是极为关键的.PostgreSQL本身已经包含了一个规划器用于寻找最优规划,我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划. PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树,其中最底层的节点为表扫描节点,用于从数据表中返回检索出的数据行.然而,不同的扫描节点类型代表着不同的表访问模式,如:顺序扫描.索引扫描,以及位图索引扫描等.如果查
-
PostgreSQL教程(八)：索引详解
一.索引的类型: PostgreSQL提供了多 种索引类型:B-Tree.Hash.GiST和GIN,由于它们使用了不同的算法,因此每种索引类型都有其适合的查询类型,缺省时,CREATE INDEX命令将创建B-Tree索引. 1. B-Tree: 复制代码 代码如下: CREATE TABLE test1 ( id integer, content varchar ); CREATE INDEX test1_id_index
-
PostgreSQL教程(二)：模式Schema详解
一个数据库包含一个或多个命名的模式,模式又包含表.模式还包含其它命名的对象,包括数据类型.函数,以及操作符.同一个对象名可以在不同的模式里使用而不会导致冲突: 比如,schema1和myschema都可以包含叫做mytable的表.和数据库不同,模式不是严格分离的:一个用户可以访问他所连接的数据库中的任意模式中的对象,只要他有权限. 我们需要模式有以下几个主要原因: 1). 允许多个用户使用一个数据库而不会干扰其它用户. 2). 把数据库对象组织成逻辑组,让它们更便于管理. 3)
-
PostgreSQL教程(四)：数据类型详解
一.数值类型: 下面是PostgreSQL所支持的数值类型的列表和简单说明: 1. 整数类型: 类型smallint.integer和bigint存储各种范围的全部是数字的数,也就是没有小数部分的数字.试图存储超出范围以外的数值将导致一个错误.常用的类型是integer,因为它提供了在范围.存储空间和性能之间的最佳平衡.一般只有在磁盘空间紧张的时候才使用smallint.而只有在integer的范围不够的时候才使用bigint,因为前者(integer)绝对快得多. 2. 任意精度数值:
-
PostgreSQL教程(九)：事物隔离介绍
在SQL的标准中事物隔离级别分为以下四种: 1. 读未提交(Read uncommitted) 2. 读已提交(Read committed) 3. 可重复读(Repeatable read) 4. 可串行化(Serializable) 然而PostgreSQL在9.1之前的版本中只是实现了其中两种,即读已提交和可串行化,如果在实际应用中选择了另外两种,那么PostgreSQL将会自动向更严格的隔离级别调整.在PostgreSQL v9.1的版本中提供了三
-
PostgreSQL教程(十三)：数据库管理详解
一.概述: 数据库可以被看成是SQL对象(数据库对象)的命名集合,通常而言,每个数据库对象(表.函数等)只属于一个数据库.不过对于部分系统表而言,如pg_database,是属于整个集群的.更准确地说,数据库是模式的集合,而模式包含表.函数等SQL对象.因此完整的对象层次应该是这样的:服务器.数据库.模式.表或其他类型的对象. 在与数据库服务器建立连接时,该连接只能与一个数据库形成关联,不允许在一个会话中进行多个数据库的访问.如以postgres用户登录,该用户可以访问的缺省数据库为postgr
-
PostgreSQL教程(一)：数据表详解
一.表的定义: 对于任何一种关系型数据库而言,表都是数据存储的最核心.最基础的对象单元.现在就让我们从这里起步吧. 1. 创建表: 复制代码 代码如下: CREATE TABLE products ( product_no integer, name text, price numeric ); 2. 删除表: 复制代码 代码如下: DROP TABLE products; 3. 创建带有缺省值的表: 复
-
PostgreSQL教程(六)：函数和操作符详解（2）
六.模式匹配: PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式. 1. LIKE: 复制代码 代码如下: string LIKE pattern [ ESCAPE escape-character ] string NOT LIKE pattern [ ESCAPE escape-character ] 每个pattern定义一个字串的集合.如果该string包含在pattern代
-
PostgreSQL教程(十四)：数据库维护
一.恢复磁盘空间: 在PostgreSQL中,使用delete和update语句删除或更新的数据行并没有被实际删除,而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期.因此当数据表中的数据变化极为频繁时,那么在一段时间之后该表所占用的空间将会变得很大,然而数据量却可能变化不大.要解决该问题,需要定期对数据变化频繁的数据表执行VACUUM操作. VACUUM命令存在两种形式,VACUUM和VACUUM FULL,它们之间的区别见如下表格: 无VACUUM VACUUM VACUUM