基于PostgreSQL的sql

表备份
select * into price_list1708151648 from price_list;

---日期格式化---
to_char(birthdate, 'yyyy-MM-dd')  返回string    'yyyy-MM-dd HH24:mi:ss'
to_date(to_char(birthdate, 'yyyy-MM-dd'),'yyyy-MM-dd')  返回date

----函数----
coalesce(num,0)  num如果是null返回0
round(num,2);   四舍五入保留两位小数
floor(num);     向下取整 （类型不变，double还是double）
age(date1,date2); 返回如：16 years 7 mons 8 days
cast(3.33  as int4)  类型转换，如转为整型，返回3

ROW_NUMBER() OVER()  行号

-----更新----
update war_stqty_lst set unit=a.leastunit from (select b.id,b.leastunit from pub_drug b) a
where war_stqty_lst.unit is null and a.id=war_stqty_lst.drugid

--插入
INSERT into o2o_other_retail_goods (channel,goods_id,retail_id,o2o_spec_id,defprice,_version)
select aa.* from (select 2 channel,b.goodsid goods_id,a.id retail_id,b.o2o_spec_id,b.defprice,1 _version
 from (select id from pub_retail where mt_flag=true and status=1) a,a1_test b) aa

------求年龄------
select id,username,birthdate,(('2017-07-20'-to_date(to_char(birthdate, 'yyyy-MM-dd'),'yyyy-MM-dd'))/365) as age from pub_user;//方法一

SELECT cast(extract(year from age(current_date,birthdate)) as int4) as age from pub_user;//方法二

select (case when a.years=0 and a.mons=0 and a.days=0 then ''
when a.years=0 and a.m