【PostgreSQL】常用函数

最新推荐文章于 2024-08-10 22:40:57 发布

csdn_hff

最新推荐文章于 2024-08-10 22:40:57 发布

阅读量825

点赞数

分类专栏： Database

Database 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

|| 字符串拼接
语法
string || string
示例
'Post' || 'greSQL' -- 返回PostgreSQL

length() 字符串的长度
语法
length(string)
示例
length('Odoo') -- 返回4

LIKE 模式匹配
语法
string LIKE pattern
示例
’abc’ LIKE ’abc’ -- 返回true
’abc’ LIKE ’a%’ -- 返回true

to_char() 把时间戳转换成字符串
语法
to_char(timestamp, text)
示例
to_char(create_date, 'YYYY/MM/DD')
to_char(create_date, ’HH12:MI:SS’)

to_date() 把字符串转换成日期
语法
to_date(text, text)
示例
to_date(’05 Jan 2015’,’DD Mon YYYY’)

to_timestamp() 把字符串转换成时间戳
语法
to_timestamp(text, text)
示例
to_timestamp(’05 Jan 2015’, ’DD Mon YYYY’)

CASE 条件表达式 , 类似于其他编程语言中的if/else
语法1
CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
示例1
CASE WHEN gender='male' THEN '程序猿' ELSE '程序媛' END
语法2(简化形式)
CASE expression WHEN value THEN result [WHEN ...] [ELSE result] END
示例2
CASE gender WHEN 'male' THEN '程序猿' ELSE '程序媛' END

COALESCE() 返回第一个非NULL的参数，所有参数均为NULL时则返回NULL
语法
COALESCE(value [, ...])
示例
COALESCE(actual_qty,0) as actual_qty

NULLIF() 如果value1与value2相等则返回NULL, 否则返回value1
语法
NULLIF(value1, value2)
示例
NULLIF(value, ’(none)’)

ascii() 将参数的第一个字符转换为ASCII码
语法
ascii(string)
示例
ascii(’x’) -- 返回120

chr() 将ASCII码转换为字符
语法
chr(int)
示例

chr(65) -- 返回A

//--------------------------------------------------------------------------------------------------------------

函数：string || string
说明：String concatenation 字符串连接操作
例子：'Post' || 'greSQL' ＝ PostgreSQL

函数：string || non-string or non-string || string
说明：String concatenation with one non-string input 字符串与非字符串类型进行连接操作
例子：'Value: ' || 42 = Value: 42

函数：bit_length(string)
说明：Number of bits in string 计算字符串的位数
例子：bit_length('jose') = 32

函数：char_length(string) or character_length(string)
说明：Number of characters in string 计算字符串中字符个数
例子：char_length('jose') = 4

函数：lower(string)
说明：Convert string to lower case 转换字符串为小写
例子：bit_length('jose') = 32

函数：octet_length(string)
说明：Number of bytes in string 计算字符串的字节数
例子：octet_length('jose') = 4

函数：overlay(string placing string from int [for int])
说明：Replace substring 替换字符串中任意长度的子字串为新字符串
例子：overlay('Txxxxas' placing 'hom' from 2 for 4) = 4

函数：position(substring in string)
说明：Location of specified substring 子串在一字符串中的位置
例子：position('om' in 'Thomas') = 3

函数：substring(string [from int] [for int])
说明：Extract substring 截取任意长度的子字符串
例子：substring('Thomas' from 2 for 3) = hom

函数：substring(string from pattern)
说明：Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching. 利用正则表达式对一字符串进行任意长度的字串的截取
例子：substring('Thomas' from '...$') = mas

函数：substring(string from pattern for escape)
说明：Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching. 利于正则表达式对某类字符进行删除，以得到子字符串
例子：trim(both 'x' from 'xTomxx') = Tom

函数：trim([leading | trailing | both] [characters] from string)
说明：Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string 去除尽可能长开始，结束或者两边的某类字符，默认为去除空白字符，当然可以自己指定，可同时指定多个要删除的字符串
例子：trim(both 'x' from 'xTomxx') = Tom

函数：upper(string)
说明：Convert string to uppercase 将字符串转换为大写
例子：upper('tom') = TOM

函数：ascii(string)
说明：ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings. the argument must be a strictly ASCII character. 得到某一个字符的Assii值
例子：ascii('x') = 120

函数：btrim(string text [, characters text])
说明：Remove the longest string consisting only of characters in characters (a space by default) from the start and end of string 去除字符串两边的所有指定的字符，可同时指定多个字符
例子：btrim('xyxtrimyyx', 'xy') = trim

函数：chr(int)
说明：Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate a strictly ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes. 得到某ACSII值对应的字符
例子：chr(65) = A

函数：convert(string bytea, src_encoding name, dest_encoding name)
说明：Convert string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See Table 9-7 for available conversions. 转换字符串编码，指定源编码与目标编码
例子：convert('text_in_utf8', 'UTF8', 'LATIN1') = text_in_utf8 represented in ISO 8859-1 encoding

函数：convert_from(string bytea, src_encoding name)
说明：Convert string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. 转换字符串编码，自己要指定源编码,目标编码默认为数据库指定编码，
例子：convert_from('text_in_utf8', 'UTF8') = text_in_utf8 represented in the current database encoding

函数：convert_to(string text, dest_encoding name)
说明：Convert string to dest_encoding.转换字符串编码，源编码默认为数据库指定编码，自己要指定目标编码,
例子：convert_to('some text', 'UTF8') = some text represented in the UTF8 encoding

函数：decode(string text, type text)
说明：Decode binary data from string previously encoded with encode. Parameter type is same as in encode. 对字符串按指定的类型进行解码
例子：decode('MTIzAAE=', 'base64') = 123\000\001

函数：encode(data bytea, type text)
说明：Encode binary data to different representation. Supported types are: base64, hex, escape. Escape merely outputs null bytes as \000 and doubles backslashes. 与decode相反，对字符串按指定类型进行编码
例子：encode(E'123\\000\\001', 'base64') = MTIzAAE=

函数：initcap(string)
说明：Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. 将字符串所有的单词进行格式化，首字母大写，其它为小写
例子：initcap('hi THOMAS') = Hi Thomas

函数：length(string)
说明：Number of characters in string 讲算字符串长度
例子：length('jose') = 4

函数：length(stringbytea, encoding name )
说明：Number of characters in string in the given encoding. The string must be valid in this encoding. 计算字符串长度，指定字符串使用的编码
例子：length('jose', 'UTF8') = 4

函数：lpad(string text, length int [, fill text])
说明：Fill up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right). 对字符串左边进行某类字符自动填充，即不足某一长度，则在左边自动补上指定的字符串，直至达到指定长度，可同时指定多个自动填充的字符
例子：lpad('hi', 5, 'xy') = xyxhi

函数：ltrim(string text [, characters text])
说明：Remove the longest string containing only characters from characters (a space by default) from the start of string 删除字符串左边某一些的字符，可以时指定多个要删除的字符
例子：trim

函数：md5(string)
说明：Calculates the MD5 hash of string, returning the result in hexadecimal 将字符串进行md5编码
例子：md5('abc') = 900150983cd24fb0 d6963f7d28e17f72

函数：pg_client_encoding()
说明：Current client encoding name 得到pg客户端编码
例子：pg_client_encoding() = SQL_ASCII

函数：quote_ident(string text)
说明：Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if

//--------------------------------------------------------------------------------------------------------------

9.13. 条件表达式

本节描述在 PostgreSQL 里可以用的SQL兼容的条件表达式。

提示: 如果你的需求超过这些条件表达式的能力，你可能会希望用一种更富表现力的编程语言写一个存储过程。

9.13.1. `CASE`

SQL CASE 表达式是一种通用的条件表达式，类似于其它语言中的 if/else 语句。

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

CASE 子句可以用于任何表达式可以有效存在的地方。 condition 是一个返回boolean 的表达式。如果结果为真，那么 CASE 表达式的结果就是符合条件的 result。如果结果为假，那么以相同方式搜寻任何随后的 WHEN 子句。如果没有 WHEN condition 为真，那么 case 表达式的结果就是在 ELSE 子句里的值。如果省略了 ELSE 子句而且没有匹配的条件，结果为 NULL。

例子：

SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
	    WHEN a=2 THEN 'two'
	    ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

所有 result 表达式的数据的类型都必须可以转换成单一的输出类型。参阅 Section 10.5 获取细节。

下面这个"简单的" CASE 表达式是上面的通用形式的一个特殊的变种。

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

先计算 expression 的值，然后与所有在WHEN 子句里声明的 value 对比，直到找到一个相等的。如果没有找到匹配的，则返回在 ELSE 子句里的 result （或者 NULL）。这个类似于 C 里的 switch 语句。

上面的例子可以用简单 CASE 语法来写：

SELECT a,
       CASE a WHEN 1 THEN 'one'
	      WHEN 2 THEN 'two'
	      ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

CASE 表达式并不计算任何对于判断结果并不需要的子表达式。比如，下面是一个可以避免被零除的方法：

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

9.13.2. `COALESCE`

COALESCE(value[, ...])

COALESCE 返回它的第一个非 NULL 的参数的值。它常用于在为显示目的检索数据时用缺省值替换 NULL 值。比如：

SELECT COALESCE(description, short_description, '(none)') ...

和 CASE 表达式一样，COALESCE 将不会计算不需要用来判断结果的参数；也就是说，在第一个非空参数右边的参数不会被计算。

9.13.3. `NULLIF`

NULLIF(value1, value2)

当且仅当 value1 和 value2 相等时，NULLIF 才返回 NULL。否则它返回 value1。这些可以用于执行上面给出的 COALESCE 例子的反例：

SELECT NULLIF(value, '(none)') ...

9.13.4. `GREATEST` 和 `LEAST`

GREATEST(value [, ...])

LEAST(value [, ...])

GREATEST 和 LEAST 函数从一个任意的数字表达式列表里选取最大或者最小的数值。这些表达式必须都可以转换成一个普通的数据类型，它将会是结果类型（参阅 Section 10.5 获取细节）。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。

请注意 GREATEST 和 LEAST 都不是 SQL 标准，但却是很常见的扩展。