postgreSQL备忘

获取今天是周几：extract(DOW FROM now())

获取今天是这个月的第几天：extract(DAY FROM now())

三周前：select now() - interval '3 week';

一天前：select current_date - 1

null值转换函数：COALESCE(num,0)

多条变一条：select groupid,string_agg(deviceid::text,','::text) from vms_store_group_devices group by groupid;

一条变多条（xml）： select (unnest(xpath('/map/zh/text()',name))) from goods_list

一条变多条(逗号分隔的)：SELECT r.id,UNNEST(string_to_array(r.device_list,','))::INTEGER as device_id FROM vms_check_records r

日期时间戳转换为long型毫秒数：SELECT EXTRACT(EPOCH FROM TIMESTAMP  '2014-07-25 10:05:21')

把long型秒数转换为时间戳类型：TO_TIMESTAMP(1405057985000/1000);//如果是毫秒数请除以1000

添加大于0的约束：alter table vms_device_stock add constraint check_more_than_zero check(goods_num >= 0);

触发器函数：

CREATE OR REPLACE FUNCTION update_device_stock() RETURNS TRIGGER  AS $BODY$

DECLARE

deviceid integer;

BEGIN

SELECT d.device_id INTO deviceid FROM vms_device_stock d WHERE d.device_id = NEW.device_id AND delete_flag = 0;

IF deviceid ISNULL THEN INSERT INTO vms_device_stock (device_id,goods_id,goods_num) VALUES (NEW.device_id,NEW.goods_id,NEW.change_num) ;

ELSE UPDATE vms_device_stock SET goods_num = (goods_num + NEW.change_num) WHERE device_id = NEW.device_id AND goods_id