postgresql整理，postgresql
数据类型:
整型： smallint integer bigint
任意精度数值： numeric(precision,scale) 精度、刻度
浮点型： real
序号类型(自增)： serial bigserial
字符类型：varchar(n) char(n) text
日期类型：timestamp8字节 2013-05-17 13:01:38.437925
Interval 12字节 555283:40:10
date 4字节 2013-05-17
time 8字节 13:01:53.890859
数组类型：integer[] 存储 array[21000,22000,23000,24000]
函数：
数学函数：
|
函数 |
返回类型 |
描述 |
例子 |
结果 |
|
abs(x) |
|
绝对值 |
abs(-17.4) |
17.4 |
|
cbrt(double) |
|
立方根 |
cbrt(27.0) |
3 |
|
ceil(double/numeric) |
|
不小于参数的最小的整数 |
ceil(-42.8) |
-42 |
|
degrees(double) |
|
把弧度转为角度 |
degrees(0.5) |
28.6478897565412 |
|
exp(double/numeric) |
|
自然指数 |
exp(1.0) |
2.71828182845905 |
|
floor(double/numeric) |
|
不大于参数的最大整数 |
floor(-42.8) |
-43 |
|
ln(double/numeric) |
|
自然对数 |
ln(2.0) |
0.693147180559945 |
|
log(double/numeric) |
|
10为底的对数 |
log(100.0) |
2 |
|
log(b numeric,x numeric) |
|
numeric指定底数的对数 |
log(2.0, 64.0) |
6.0000000000 |
|
mod(y, x) |
|
取余数 |
mod(9,4) |
1 |
|
pi() |
double |
"π"常量 |
pi() |
3.14159265358979 |
|
power(a double, b double) |
double |
求a的b次幂 |
power(9.0, 3.0) |
729 |
|
power(a numeric, b numeric) |
numeric |
求a的b次幂 |
power(9.0, 3.0) |
729 |
|
radians(double) |
double |
把角度转为弧度 |
radians(45.0) |
0.785398163397448 |
|
random() |
double |
0.0到1.0之间的随机数值 |
random() |
|
|
round(double/numeric) |
|
圆整为最接近的整数 |
round(42.4) |
42 |
|
round(v numeric, s int) |
numeric |
圆整为s位小数数字 |
round(42.438,2) |
42.44 |
|
sign(double/numeric) |
|
参数的符号(-1,0,+1) |
sign(-8.4) |
-1 |
|
sqrt(double/numeric) |
|
平方根 |
sqrt(2.0) |
1.4142135623731 |
|
trunc(double/numeric) |
|
截断(向零靠近) |
trunc(42.8) |
42 |
|
trunc(v numeric, s int) |
numeric |
截断为s小数位置的数字 |
trunc(42.438,2) |
42.43 |
三角函数：
|
函数 |
描述 |
|
acos(x) |
反余弦 |
|
asin(x) |
反正弦 |
|
atan(x) |
反正切 |
|
atan2(x, y) |
正切 y/x 的反函数 |
|
cos(x) |
余弦 |
|
cot(x) |
余切 |
|
sin(x) |
正弦 |
|
tan(x) |
正切 |
字符串函数：
|
函数 |
返回类型 |
描述 |
例子 |
结果 |
|
string || string |
text |
字串连接 |
'Post' || 'greSQL' |
PostgreSQL |
|
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 |
|
overlay(string placing string from int [for int]) |
text |
替换子字串 |
overlay('Txxxxas' placing 'hom' from 2 for 4) |
Thomas |
|
substring(string [from int] [for int]) |
text |
抽取子字串 |
substring('Thomas' from 2 for 3) |
hom |
|
substring(string from pattern) |
text |
抽取匹配 POSIX正则表达式的子字串 |
substring('Thomas' from '...$') |
mas |
|
trim([leading | trailing | both] [characters] from string) |
text |
从字串string的开头/结尾/两边/删除只包含characters(缺省是一个空白)的最长的字串 |
trim(both 'x' from 'xTomxx') |
Tom |
|
upper(string) |
text |
把字串转化为大写。 |
upper('tom') |
TOM |
|
btrim(string text [, characters text]) |
text |
从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串 |
btrim('xyxtrimyyx','xy') |
trim |
|
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 |
|
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 |
|
translate(string text, from text, to text) |
text |
把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。 |
translate('12345', '14', 'ax') |
a23x5 |
数据格式函数：
|
函数 |
返回类型 |
描述 |
例子 |
|
to_char(timestamp, text) |
text |
把时间戳转换成字串 |
to_char(current_timestamp, 'HH12:MI:SS') |
|
to_char(interval, text) |
text |
把时间间隔转为字串 |
to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
|
to_char(int, text) |
text |
把整数转换成字串 |
to_char(125, '999') |
|
to_char(double precision, text) |
text |
把实数/双精度数转换成字串 |
to_char(125.8::real, '999D9') |
|
to_char(numeric, text) |
text |
把numeric转换成字串 |
to_char(-125.8, '999D99S') |
|
to_date(text, text) |
date |
把字串转换成日期 |
to_date('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(text, text) |
timestamp |
把字串转换成时间戳 |
to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
|
to_timestamp(double) |
timestamp |
把UNIX纪元转换成时间戳 |
to_timestamp(200120400) |
|
to_number(text, text) |
numeric |
把字串转换成numeric |
to_number('12,454.8-', '99G999D9S') |
日期/时间函数：
|
函数 |
返回类型 |
描述 |
例子 |
结果 |
|
age(timestamp, timestamp) |
interval |
减去参数，生成一个使用年、月的"符号化"的结果 |
age('2001-04-10', timestamp '1957-06-13') |
43 years 9 mons 27 days |
|
age(timestamp) |
interval |
从current_date减去得到的数值 |
age(timestamp '1957-06-13') |
43 years 8 mons 3 days |
|
current_date |
date |
今天的日期 |
|
|
|
current_time |
time |
现在的时间 |
|
|
|
current_timestamp |
timestamp |
日期和时间 |
|
|
|
date_part(text, timestamp) |
double |
获取子域(等效于extract) |
date_part('hour', timestamp '2001-02-16 20:38:40') |
20 |
|
date_part(text, interval) |
double |
获取子域(等效于extract) |
date_part('month', interval '2 years 3 months') |
3 |
|
date_trunc(text, timestamp) |
timestamp |
截断成指定的精度 |
date_trunc('hour', timestamp '2001-02-16 20:38:40') |
2001-02-16 20:00:00+00 |
|
extract(field from timestamp) |
double |
获取子域 |
extract(hour from timestamp '2001-02-16 20:38:40') |
20 |
|
extract(field from interval) |
double |
获取子域 |
extract(month from interval '2 years 3 months') |
3 |
|
localtime |
time |
今日的时间 |
|
|
|
localtimestamp |
timestamp |
日期和时间 |
|
|
|
now() |
timestamp |
当前的日期和时间(等效于 current_timestamp) |
|
|
|
timeofday() |
text |
当前日期和时间 |
|
|
EXTRACT、date_part函数支持的field：
|
域 |
描述 |
例子 |
结果 |
|
DAY |
(月分)里的日期域(1-31) |
EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40'); |
16 |
|
DOW |
每周的星期号(0-6；星期天是0) (仅用于timestamp) |
EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); |
5 |
|
DOY |
一年的第几天(1 -365/366) (仅用于 timestamp) |
EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40'); |
47 |
|
HOUR |
小时域(0-23) |
EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40'); |
20 |
|
MICROSECONDS |
秒域，包括小数部分，乘以 1,000,000。 |
EXTRACT(MICROSECONDS from TIME '17:12:28.5'); |
28500000 |
|
MILLISECONDS |
秒域，包括小数部分，乘以 1000。 |
EXTRACT(MILLISECONDS from TIME '17:12:28.5'); |
28500 |
|
MINUTE |
分钟域(0-59) |
EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40'); |
38 |
|
MONTH |
对于timestamp数值，它是一年里的月份数(1-12)；对于interval数值，它是月的数目，然后对12取模(0-11) |
EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40'); |
2 |
|
QUARTER |
该天所在的该年的季度(1-4)(仅用于 timestamp) |
EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40'); |
1 |
|
SECOND |
秒域，包括小数部分(0-59[1]) |
EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40'); |
40 |
|
WEEK |
该天在所在的年份里是第几周。 |
EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); |
7 |
|
YEAR |
年份域 |
EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); |
2001 |
数组函数;
|
函数 |
返回类型 |
描述 |
例子 |
结果 |
|
array_cat(anyarray, anyarray) |
anyarray |
连接两个数组 |
array_cat(ARRAY[1,2,3], ARRAY[4,5]) |
{1,2,3,4,5} |
|
array_append(anyarray, anyelement) |
anyarray |
向一个数组末尾附加一个元素 |
array_append(ARRAY[1,2], 3) |
{1,2,3} |
|
array_prepend(anyelement, anyarray) |
anyarray |
向一个数组开头附加一个元素 |
array_prepend(1, ARRAY[2,3]) |
{1,2,3} |
|
array_dims(anyarray) |
text |
返回一个数组维数的文本表示 |
array_dims(ARRAY[[1,2,3], [4,5,6]]) |
[1:2][1:3] |
|
array_lower(anyarray, int) |
int |
返回指定的数组维数的下界 |
array_lower(array_prepend(0, ARRAY[1,2,3]), 1) |
0 |
|
array_upper(anyarray, int) |
int |
返回指定数组维数的上界 |
array_upper(ARRAY[1,2,3,4], 1) |
4 |
|
array_to_string(anyarray, text) |
text |
使用提供的分隔符连接数组元素 |
array_to_string(ARRAY[1, 2, 3], '~^~') |
1~^~2~^~3 |
|
string_to_array(text, text) |
text[] |
使用指定的分隔符把字串拆分成数组元素 |
string_to_array('xx~^~yy~^~zz', '~^~') |
{xx,yy,zz} |
聚合函数:
1. AVG 返回指定组中的平均值，空值被忽略。 例：select prd_no,avg(qty) from sales group by prd_no 2. COUNT 返回指定组中项目的数量。 例：select count(prd_no) from sales 3. MAX 返回指定数据的最大值。 例：select prd_no,max(qty) from sales group by prd_no 4. MIN 返回指定数据的最小值。 例：select prd_no,min(qty) from sales group by prd_no 5. SUM 返回指定数据的和，只能用于数字列，空值被忽略。 例：select prd_no,sum(qty) from sales group by prd_no 6. COUNT_BIG 返回指定组中的项目数量，与COUNT函数不同的是COUNT_BIG返回bigint值，而COUNT返回的是int值。 例：select count_big(prd_no) from sales 7. GROUPING 产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，输出值为1.当所添加的行不是由CUBE或ROLLUP产生时，输出值为0. 例：select prd_no,sum(qty),grouping(prd_no) from sales group by prd_no with rollup 8. BINARY_CHECKSUM 返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行的更改。 例：select prd_no,binary_checksum(qty) from sales group by prd_no 9. CHECKSUM_AGG 返回指定数据的校验值，空值被忽略。 例：select prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_no 10. CHECKSUM 返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。 11. STDEV 返回给定表达式中所有值的统计标准偏差。 例：select stdev(prd_no) from sales 12. STDEVP 返回给定表达式中的所有值的填充统计标准偏差。 例：select stdevp(prd_no) from sales 13. VAR 返回给定表达式中所有值的统计方差。 例：select var(prd_no) from sales 14. VARP 返回给定表达式中所有值的填充的统计方差。 例：select varp(prd_no) from sales
15.统计各部门的总薪水，平均薪水和部门的详细情况
SELECTsum(salary) OVER (PARTITION by depname),avg(salary) OVER (PARTITION bydepname),*from empsalary;
16.统计人员在所在部门的薪水排名情况
select rank()over(partition by depname ORDER BY salary),* from empsalary;
select row_number() over(PARTITION by depnameorder by salary desc),* from empsalary;
为每一行指派一个唯一的编号。使用此函数替代 NUMBER 函数。
select rank() over(partition by depname orderby salary desc),* from empsalary;
计算一个值在一组值中的排位。如果出现并列的情况，RANK 函数会在排名序列中留出空位。
select dense_rank() over(partition by depnameorder by salary desc),* from empsalary;
计算值在分区中的排位。对于并列的值，DENSE_RANK 函数不会在排名序列中留出空位。
select percent_rank() over(partition bydepname order by salary desc),* from empsalary;
根据rank()排序的第X行减去1除于组中总记录-1.
select cume_dist() over(partition by depnameorder by salary desc),* from empsalary;
计算某个值在一组行中的相对位置。
select ntile(3) over (partition by depnameorder by salary desc),* from empsalary;
从1到当前值，除以分组的的数量，尽可能使分布平均
select lag(salary,2,NULL) over(partition bydepname order by salary desc),* from empsalary;
偏移量函数，取滞后值,如lag(column_name,2,0)表示字段偏移量为2，没有则用default值代替，这里是0，不写默认是null
select first_value(salary) over(partition bydepname order by salary desc),* from empsalary;
从窗口第一行返回值。
注释
FIRST_VALUE函数使您不用自连接就能（依照某种排序）选择表中的第一个值。如果您希望使用第一个值作为计算的基准，此函数很有价值。
FIRST_VALUE函数从窗口提取第一个记录。然后针对第一个记录计算 expression 并返回结果。
如果指定 IGNORE NULL，则返回 expression 的第一个非 NULL 值。如果指定 RESPECT NULLS（缺省值），则会返回第一个值，无论其是否为 NULL。
FIRST_VALUE 函数与其它大多数集合函数的不同之处在于：该函数只能与窗口说明一起使用。
select last_value(salary) over (partition bydepname ),* from empsalary;
从窗口最后一行返回值。
注释
LAST_VALUE函数使您不用自连接就能（依照某种排序）选择表中的最后一个值。如果您希望使用最后一个值作为计算的基准，此函数很有价值。
LAST_VALUE函数在完成 ORDER BY 后从分区提取最后一个记录。然后针对最后一个记录计算 expression 并返回结果。
如果指定 IGNORE NULL，则返回 expression 的最后一个非 NULL 值。如果指定 RESPECT NULLS（缺省值），则会返回最后一个值，无论其是否为 NULL。
LAST_VALUE函数与其它大多数集合函数的不同之处在于：该函数只能与窗口说明一起使用。
select nth_value(salary,2) over (partition by depname order by salary),* fromempsalary;
返回窗口框架中的指定值，如nth_value(salary,2),则表示返回字段salary的第二个窗口函数值
主意last_value()和first_value()函数的运用，当使用order by 只会对当前行排序。例如：
修改为order by的范围：
