PGSQL_实操常用指南

1、DDL操作

1.1、建表

--1、表不存在->创建
--2、给注释
CREATE TABLE IF NOT EXISTS public.pro_salary_quota (
        id varchar(50) NOT NULL, -- 主键Id
        working_process_type int4 NOT NULL, -- 工序类型：1-构件工序，2-零件工序
        working_process_id varchar(50) NULL, -- 工序id
        valuation_unit int4 NOT NULL, -- 核价单位
        unit_price numeric(20, 2) NOT NULL, -- 单位单价
        factory_id varchar(50) NULL, -- 工厂id
        remark varchar(50) NULL, -- 备注
        create_userid varchar(50) NULL, -- 创建人ID
        modify_userid varchar(50) NULL, -- 修改人ID
        create_username varchar(50) NULL, -- 创建人
        modify_username varchar(50) NULL, -- 修改人
        create_date timestamp NULL, -- 创建日期
        modify_date timestamp NULL, -- 修改日期
        is_deleted bool NOT NULL, -- 是否删除
        autoincrm_id serial4 NOT NULL, -- 自增ID，排序使用
        CONSTRAINT pro_salary_quota_pkey PRIMARY KEY (id)
);
COMMENT ON TABLE public.pro_salary_quota IS '定额配置主表';
COMMENT ON COLUMN public.pro_salary_quota.id IS '主键Id';
COMMENT ON COLUMN public.pro_salary_quota.working_process_type IS '工序类型：1-构件工序，2-零件工序';
COMMENT ON COLUMN public.pro_salary_quota.working_process_id IS '工序id';
COMMENT ON COLUMN public.pro_salary_quota.valuation_unit IS '核价单位';
COMMENT ON COLUMN public.pro_salary_quota.unit_price IS '单位单价';
COMMENT ON COLUMN public.pro_salary_quota.factory_id IS '工厂id';
COMMENT ON COLUMN public.pro_salary_quota.remark IS '备注';
COMMENT ON COLUMN public.pro_salary_quota.create_userid IS '创建人ID';
COMMENT ON COLUMN public.pro_salary_quota.modify_userid IS '修改人ID';
COMMENT ON COLUMN public.pro_salary_quota.create_username IS '创建人';
COMMENT ON COLUMN public.pro_salary_quota.modify_username IS '修改人';
COMMENT ON COLUMN public.pro_salary_quota.create_date IS '创建日期';
COMMENT ON COLUMN public.pro_salary_quota.modify_date IS '修改日期';
COMMENT ON COLUMN public.pro_salary_quota.is_deleted IS '是否删除';
COMMENT ON COLUMN public.pro_salary_quota.autoincrm_id IS '自增ID，排序使用';

1.2、表增加字段

--工厂表添加字段过磅预警阈值
ALTER TABLE public.pro_salary_quota ADD IF NOT EXISTS weigh_warning_threshold numeric(20, 2) NULL;
COMMENT ON COLUMN public.pro_factory.weigh_warning_threshold IS '过磅预警阈值';

1.3、表删除字段

ALTER TABLE public.pro_salary_quota DROP IF EXISTS tare;

1.4、表修改字段不可为null

ALTER TABLE public.materiel_raw_store_sub alter in_store_count set not NULL;

1.5、表修改字段可为null

ALTER TABLE public.materiel_raw_store_sub alter in_store_weight drop not NULL;

2、常用日期格式

--获取本天  yyyy-MM-dd   /2022-12-13
select current_date;

--获取当前时间    16:39:37 +0800
select current_time 

--获取本天  date    /2022-12-13
select  now();

--2023-03-21 16:40:09.413 +0800
select current_timestamp					

--2023-03-21 16:42:11.352
select now()::timestamp without time zone	

--获取本天  /13
select date_part('day', current_timestamp)
--格式化 年月日-时  /2022-12-13 16
SELECT to_char(now(),'yyyy-MM-dd hh24');

--格式化 年月日-时分 /2022-12-13 16:17
SELECT to_char(now(),'yyyy-MM-dd hh24:MI');

--格式化 年月日-时分秒  /2022-12-13 16:17:53
SELECT to_char(now(),'yyyy-MM-dd hh24:MI:ss');

--格式化 年月日  /20221213
select to_char(current_date,'yyyymmdd');

--格式化 年月日 2022年12月13日
select to_char(current_date,'yyyy年mm月dd日');

--获取本月第一天  /2022-12-01 00:00:00.000 +0800
select date_trunc('month',now());


--获取本月最后一天 /2022-12-31 00:00:00.000 +0800
select date_trunc('month',now()) +interval '1 month' -interval '1 day';


--获取本月月份  /12
select date_part('month', current_timestamp)


--获取本年第一天  /2022-01-01 00:00:00.000 +0800
select date_trunc( 'year', now() ); 


--获取本年最后一天  /2022-12-31 00:00:00.000 +0800
select date_trunc( 'year', now() )+interval '1 year' -interval '1 day';

--获取本年年份 /2022
select date_part('year', current_timestamp);

--获取 上一天的日期  /  2022-12-12 16:30:32.481 +0800
select now() - interval '1 day';

--格式化日期  /2022-12-12 16:31:22  以下同理
SELECT to_char(now() - interval '1 day','yyyy-MM-dd hh24:MI:ss');

--获取 上一周的日期  /2022-12-06 16:31:42.240 +0800
select now() - interval '1 week';

--获取 上一个月的日期  /2022-11-13 16:31:56.145 +0800
select now() - interval '1 month';

--获取 一年前的日期 2021-12-13 16:31:56.145 +0800
select now() - interval '1 year';

3、语法

3.1、行转列（交叉表）

select split_part(code, '~', 1) as Project_Name, split_part(code, '~', 2) as Project_Code, split_part(code, '~', 3) as Area_Name,  "ZGJ" ,  "LGJ" ,  "CGJ" ,  "HHZ" ,  "RHZ" ,  "SC" ,  "total"   
from crosstab('
    select project_name||''~'' || project_code ||''~''||area_name,type, yield
    from(
        select max(e.short_name) as project_name, a.project_code, max(case when coalesce(d.parentid,'''')='''' then d.name else concat(ppa.name,''/'', d.name) end) as area_name, a.sys_project_id, a.type,
            a.category_value,round(sum(a.yield * a.component_count) / max(coalesce(f.proportion,1)),3) as yield, max(f.unit) as unit
        from pro_produced_component a
        left join pro_factory pf on a.factory_id = pf.id
        left join pro_component_type b on a.type = b.code and coalesce(pf.company_id, '''') =  coalesce(b.company_id, '''')
        left join pro_project_area d on a.area_id = d.id
        left join pro_project_area ppa on d.parentid = ppa.id
        left join pro_project e on a.sys_project_id = e.sys_project_id
        left join plm_professional_type f on a.category_value = f.code and coalesce(pf.company_id, '''') =  coalesce(f.company_id, '''')
        where a.is_deleted = false and a.category_value = ''Steel''    and a.factory_id=''091ff09b-5944-4391-94d8-22d062c8b8df''  and a.create_date between ''2023/3/17 0:00:00'' and ''2023/4/17 0:00:00'' 
        group by a.sys_project_id, a.project_code, a.area_id, a.type, a.category_value
        
        union all
         --合计
        
        select max(e.short_name) as project_name, a.project_code, max(case when coalesce(d.parentid,'''')='''' then d.name else concat(ppa.name,''/'', d.name) end) as area_name, a.sys_project_id, ''total''::text ,
            a.category_value,round(sum(a.yield * a.component_count) / max(coalesce(f.proportion,1)),2) as yield, max(f.unit) as unit
        from pro_produced_component a
        left join pro_factory pf on a.factory_id = pf.id
        left join pro_component_type b on a.type = b.code and coalesce(pf.company_id, '''') =  coalesce(b.company_id, '''')
        left join pro_project_area d on a.area_id = d.id
        left join pro_project_area ppa on d.parentid = ppa.id
        left join pro_project e on a.sys_project_id = e.sys_project_id
        left join plm_professional_type f on a.category_value = f.code and coalesce(pf.company_id, '''') =  coalesce(f.company_id, '''')
        where a.is_deleted = false and a.category_value = ''Steel''  and a.factory_id=''091ff09b-5944-4391-94d8-22d062c8b8df''  and a.create_date between ''2023/3/17 0:00:00'' and ''2023/4/17 0:00:00'' 
        group by a.sys_project_id, a.project_code, a.area_id, a.category_value
    ) c 
    where 1= 1 
    order by 1, 2 ',
    $$ values   ('ZGJ'::text),  ('LGJ'::text),  ('CGJ'::text),  ('HHZ'::text),  ('RHZ'::text),  ('SC'::text),  ('total'::text)$$) 
as ct (code text,  "ZGJ" numeric,  "LGJ" numeric,  "CGJ" numeric,  "HHZ" numeric,  "RHZ" numeric,  "SC" numeric,  "total" numeric) 
order by    1 

3.2、串表删除

delete from AAA a
using BBB b,CCC c
where a.id=b.aid and b.id=c.bid and a.id=c.aid

3.3、串表更新

--串表更新
update AAA a
set a.name='eago'
from BBB b
inner join CCC c on b.id=c.bid
where b.aid=a.id and c.aid=a.id