postgresql命令

数据库

版本信息

show server_version;

插入

从一张表中查询出记录然后批量插入到另一张表中

INSERT INTO tableName1
(主键uuid, relation_id, relation_name)
select 
    replace(cast(uuid_generate_v4() as VARCHAR), '-', ''),
    id, 
    name
from tableName2;

自增主键

1. 创建一个序列

create sequence test_seq_id
start with 1
increment by 1
no minvalue
no maxvalue
cache 1;

2. 将表的一列的默认值设为主键的下一个值

alter table test_tabl alter column id set default nextval(‘test_seq_id’);

查询

列转行

--实现类似于group_concat的功能
select 
  array_to_string(array_agg(distinct code),',') aa,
  string_agg(code,',') bb
from code_gxs
group by code_lev2

空值排序

默认null为最大，降序排序时会把null值排在最前面，可以使用null last让null值放在最后。当然在升序时也可以使用nulls first放在最前面（不过一般不会把null放在最前面）。

select * 
from test_table
order by aa desc nulls last,
  bb asc nulls first

函数

聚合函数

select zjhm,array_to_string(array_agg(distinct asjbh),',')
from table1
group by zjhm

窗口函数

窗口函数

查询线索总数、有效数量和无效数量。线索是否有效根据最新的反馈来判断。

select
    sum(case when b.sfyx = '0' then 1 else 0 end) wxsl,
    sum(case when b.sfyx = '1' then 1 else 0 end) yxsl,
    count(0) zs
from xs a
left join (
    select xsbh,sfyx,
    row_number() over (partition by xsbh order by fksj desc) r
    from xs_fk
) b on a.bh = b.xsbh
where (b.r = 1 or b.r is null)

时间

当前时间

select now();
select current_timestamp;
-- 获取当前日期
select current_date;
-- 获取当前时间
select current_time;

转为字符串

select to_char(时间戳字段,'yyyy-mm-dd hh24:mi:ss');
-- hh默认是12，可指定：hh12，hh24

时间加减 interval

-- 当前时间减一秒
select to_char( now() - interval '1 second','yyyy-mm-dd');
-- 当前时间减一分钟
select to_char( now() - interval '1 minutes','yyyy-mm-dd');
-- 当前时间减一小时
select to_char( now() - interval '1 hous','yyyy-mm-dd');
-- 昨天
select to_char( now() - interval '1 day','yyyy-mm-dd');
-- 上周
select to_char( now() - interval '1 week','yyyy-mm-dd');
-- 上月
select to_char( now() - interval '1 month','yyyy-mm-dd');
-- 去年
select to_char( now() - interval '1 year','yyyy-mm-dd');

date_trunc

-- 当前年份的开始时间
select date_trunc('year', now())
-- 当前月的开始时间
select date_trunc('month', now())
select date_trunc('day', now())
select date_trunc('hour', now())
select date_trunc('minute', now())
select date_trunc('second', now())

date_part提取时间

-- 获得当前时间的年
select date_part('year', timestamp '2001-02-16 20:38:40')
select date_part('month', timestamp '2001-02-16 20:38:40')
select date_part('day', timestamp '2001-02-16 20:38:40')
-- 1点的时候是1
select date_part('hour', timestamp '2001-02-16 20:38:40')
select date_part('minute', timestamp '2001-02-16 20:38:40')
select date_part('second', timestamp '2001-02-16 20:38:40')
select date_part('week', timestamp '2001-02-16 20:38:40')

select date_part('year', current_timestamp)
select date_part('year', now())
select date_part('year', timestamp '2022-02-28')
select date_part('year','2022-02-28'::timestamp)
select date_part('year', 数据表中的时间戳字段名)

时间数据加减年月日时分秒

SELECT count(1) 
FROM tb_st_asj 
WHERE larq between to_timestamp(
  concat('2021-10-01',' 00:00:00'),'yyyy-MM-dd hh24:mi:ss' 
) + '-1 years' 
and to_timestamp( 
  concat ( '2021-10-31', ' 23:59:59' ), 'yyyy-MM-dd hh24:mi:ss' 
) + '-1 years' 
AND xxsc_pdbz = '0'

//会变成10.30而不是10.31
select TO_TIMESTAMP( 
    concat('2021-11-30',' 23:59:59'), 'yyyy-MM-dd hh24:mi:ss' 
) + '-1 month'

查询时间的上个月的最后一秒

select date_trunc(
    'month',
    TO_TIMESTAMP('2021-09-30 23:59:59','yyyy-MM-dd hh24:mi:ss')
) + '-1 seconds' 

查询9月的天数

select date_part(
    'days',
    date_trunc(
        'month',
        TO_TIMESTAMP(
            concat('2021-09-25',' 00:00:00'), 
            'yyyy-MM-dd hh24:mi:ss' )
     ) + '1 month'::interval - '1 day'::interval
 )

字符串

字符串转为数值

to_number('1','9')

字符串截取

select substring(zjhm,17,1)

数学计算

取余

-- 把9对2取余
mod(9,2)

空值

空值

-- 如果为null时就返回0
select xsbh,coalesce(count(0),0) from xs_fk