PostgreSQL笔记

1.时间

四种方式查询当前时间now() , current_date, current_time, current_timestamp, 其中now() 等同于 current_timestamp

1.当前时间向前推一天
SELECT now() + interval '1 day'
SELECT current_timestamp - interval '1 day'
例：求出最近三天的数据
select * from 表名 where date between （SELECT current_timestamp - interval '3 day'） and current_timestamp
2.当前时间向前推进一个月
SELECT current_timestamp - interval '1 month'
3.当前时间向前推进一年
SELECT current_timestamp - interval '1 year'
4.当前时间向前推一小时：
SELECT current_timestamp - interval '1 hour'
5.当前时间向前推一分钟：
SELECT current_timestamp - interval '1 min'
6.当前时间向前推60秒：
SELECT current_timestamp - interval '60 second'

2.字符串

1.字符串连接（或者与数字连接）
select 'a' || 12 || 'b' ; 返回：a12b
2.查询字符串中的字符个数
select char_length('a123') ; 返回：4
3.截取字符串
select substring('abcdefg' from 2 for 3) ; 返回：bcd
select position('a' in 'abc')； 返回：1
lower(), upper()等...

3.聚合函数

1.返回JSON数组
select json_agg(department_name) from prescription;
返回：["心内科", "肾内科", "心内科", "心内科", "皮肤科门诊", "心内科", "心内科", "皮肤科门诊", "心内科", "心内科", "心内科", "心内科", "心内科", "心内科", "心内科", "皮肤科门诊", "肾内科", "心内科", "心内科", "心内科", "心内科", "皮肤科门诊", "肾内科1", "内分泌科", "内分泌科", "心内科", "心内科", "心内科", "心内科", "肾内科1"]
2.返回JSON数组并去重
select json_agg(distinct(department_name)) from prescription;
返回:["内分泌科", "心内科", "皮肤科", "皮肤科门诊", "肾内科", "肾内科1"]
3.行转列 string_agg
测试表

eno | ename | job | mgr | hiredate | sal | comm | deptno
------+--------+----------+------+---------------------+------+------+--------
7499 | ALLEN | SALESMAN | 7698 | 1991-02-20 00:00:00 | 1600 | 300 | 30
7566 | JONES | MANAGER | 7839 | 1991-04-02 00:00:00 | 2975 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1991-09-28 00:00:00 | 1250 | 1400 | 30
7498 | JASON | ENGINEER | 7724 | 1990-02-20 00:00:00 | 1600 | 300 | 10

将部门为30的员工的姓名合并起来
Select deptno,string_agg(ename,',') from table group by deptno;
deptno | string_agg
----------+--------------
30 | ALLEN,MARTIN
10 | JASON
20 | JONES
例：SELECT department_id, string_agg(doctor_code,',') FROM doctor group by department_id