postgresql中常用小语法

1. PG中 类型转换

有时候在postgresql中需要对值的类型进行转换，pg中提供的方法

example : select '33':: integer

example2: select case('33' as integer)

2. pg中的行号 （类似于oracle中的 rownum）

example : select row_number() over() , * from XXXX

3. pg 中查询中的列转数组

example : select array_agg(AAAAA) FROM XXXXX

4. pg 中字符串函数 ：

函数：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)