postgresql 数据库操作点记

普通查询

查询结果拼接

	select string_agg(field)
    1.一行数据中的 多字段值根据连接符拼接
    concat_ws(':',aaa,bbb)  或者  ||
    2.几行数据中的 同一 单字段值根据连接符拼接
    string_agg(ccc,' \r\n ')
    3.如果要将多个字段的值拼接成一个：
    string_agg(concat_ws(':',aaa,bbb),' \r\n ' order by aaa asc) as xxx

查询所有字段 排除某些字段

    SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
          FROM information_schema.columns As c
            WHERE table_name = '$tablename' 
            AND  c.column_name NOT IN('createdat', 'updatedat', 'deletedat','geom')
    ), ',') || ',ST_AsText (geom) AS geom FROM $tablename As o WHERE o.deletedat IS NULL' As sqlstr

处理时间

https://blog.csdn.net/snn1410/article/details/7741283

查询结果去除null值

SELECT  coalesce(wendushis,'') as wendushis,coalesce(yalishis,'') as yalishis,coalesce(yeweiss,'') as yeweiss,coalesce(liuliang,'') as liuliang  FROM equip_risksource where deletedat is NULL AND equipid = '$equipid'

安装扩展 自动填充uuid

    -- Extension: "uuid-ossp"
    -- DROP EXTENSION "uuid-ossp";
     CREATE EXTENSION uuid-ossp
      SCHEMA public
      VERSION "1.0";
    使用时 字段默认值为 uuid_generate_v4()

数据库插入guid函数

    CREATE OR REPLACE FUNCTION "public"."new_guid"()
     RETURNS "pg_catalog"."varchar" AS $BODY$   

    DECLARE   
    
      v_seed_value varchar(32);   
    
    BEGIN   
    
    select   
    
    md5(   
    
    inet_client_addr()::varchar ||   
    
    timeofday() ||   
    
    inet_server_addr()::varchar ||   
    
    to_hex(inet_client_port())   
    
    )   
    
    into v_seed_value;
    return (substr(v_seed_value,1,8) || '-' ||   
    
            substr(v_seed_value,9,4) || '-' ||   
    
            substr(v_seed_value,13,4) || '-' ||   
    
            substr(v_seed_value,17,4) || '-' ||   
    
            substr(v_seed_value,21,12));   
    
    END;   
    
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER  COST 100
    ;
    
    ALTER FUNCTION "public"."new_guid"() OWNER TO "postgres";
    

清空表格数据

    TRUNCATE tablename RESTART IDENTITY

分组查询的GroupBy

    SELECT column-list  
    FROM table_name  
    WHERE [conditions ]  
    GROUP BY column1, column2....columnN  
    ORDER BY column1, column2....columnN  

添加字段 修改字段

    alter table equip_pipelinemanager add column texturemateria  VARCHAR(100);
    COMMENT ON COLUMN equip_pipelinemanager.texturemateria IS '管线材质';
    
    ALTER TABLE equip_risk alter status  type int USING status::int;

获取字段名、类型、注释、是否为空:

    SELECT col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as type,a.attname as name, a.attnotnull as notnull FROM pg_class as c,pg_attribute as a where c.relname = 'equip_linemanagement' and a.attrelid = c.oid and a.attnum>0

数据库创建删除导入导出

    创建：createdb -U postgres -h 127.0.0.1 -p 5432 -e base_equipment
    删除：dropdb -U postgres -h 127.0.0.1 -p 5432 -e base_equipment
    导入：psql -h 127.0.0.1 -p 5432 -d base_equipment -U postgres -f F:\sqldata\jilin\base_equipment.sql
    导出：pg_dump -U postgres -h 127.0.0.1 -p 5432  -f F:\sqldata\jilin\base_equipment.sql base_equipment

postgresql 查询批更新

    INSERT INTO public_user (SELECT useraccount, password, username, userlevel,NULL,NULL,now(),now(),deletedat,userid,useraccount,'001' FROM pub_user)
    UPDATE tb1 SET c1=b.c1 c2=b.c2 FROM b WHERE tb1.c3 = b.c3 AND tb1.c4 = b.c4
    自更新
    UPDATE pdfhots SET pdfurl=replace(pdfhots.pdfurl,'10.177.6.192','10.177.5.3')

postgresql 查询批量插入

    INSERT INTO public_user (SELECT useraccount, password, username, userlevel,NULL,NULL,now(),now(),deletedat,userid,useraccount,'001' FROM pub_user)

    第二个例子
    INSERT INTO hotsrelation(hotid,relatedotherobjecttype,relatedotherobjectid) SELECT hotid,relatedotherobjecttype,relatedotherobjectid FROM hotsrelation1

postgresql 字段类型查询 转换

     select CAST('5' as char),CAST('2015-10-10' as char),CAST('e10adc3949ba59abbe56e057f20f883e' as uuid);

postgresql删除活动链接的数据库 2019-4-28

    SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='base_graphdb1' AND pid<>pg_backend_pid();

匹配操作

    不区分大小写模糊匹配：~* 'aa'
    左侧匹配：~ '^aa'

设置自增主键

    #表public_pdfsearch已建好  主键为id
    CREATE SEQUENCE public_pdfsearch_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
    alter table public_pdfsearch alter column id set default nextval('public_pdfsearch_id_seq');

查询不区分大小写，去除重复记录，分组

    # ilike 关键字不区分大小写
    # distinct on (字段) 去除字段列的重复记录
    select distinct on (filepath)  filename,filepath,pages from public_pdfsearch  where context ilike '%IA344%' or imgtext ilike '%IA344%' or filename ilike '%IA344%'  limit 50 
    #分组
    select public_pdfsearch.filepath,   count(*) as num from public_pdfsearch  where context ilike '%陈%' or imgtext ilike '%陈%' or filename ilike '%陈%' GROUP BY filepath  ORDER BY num DESC

创建gin索引

     CREATE EXTENSION pg_trgm;
    CREATE INDEX ix_gallery_map_author ON gallery_map USING gin (author gin_trgm_ops);
    EXPLAIN ANALYZE select * from gallery_map where author like '曹%';   QUERY PLAN

postgresql 获取汉字首字母函数

创建函数

CREATE OR REPLACE FUNCTION CnFirstChar(s character varying)
 RETURNS character varying AS
$BODY$
declare
 retval character varying;
 c character varying;
 l integer;
 b bytea; 
 w integer;
begin
l=length(s);
retval='';
while l>0 loop
 c=left(s,1);
 b=convert_to(c,'GB18030')::bytea;
 if get_byte(b,0)<127 then
 retval=retval || upper(c);
 elsif length(b)=2 then
 begin
 w=get_byte(b,0)*256+get_byte(b,1);
 --汉字GBK编码按拼音排序，按字符数来查找，基于概率来说，效率应该比挨个强:)
 if w between 48119 and 49061 then --"J";48119;49061;942
 retval=retval || 'J';
 elsif w between 54481 and 55289 then --"Z";54481;55289;808
 retval=retval || 'Z';
 elsif w between 53689 and 54480 then --"Y";53689;54480;791
 retval=retval || 'Y';
 elsif w between 51446 and 52208 then --"S";51446;52208;762
 retval=retval || 'S';
 elsif w between 52980 and 53640 then --"X";52980;53640;660
 retval=retval || 'X';
 elsif w between 49324 and 49895 then --"L";49324;49895;571
 retval=retval || 'L';
 elsif w between 45761 and 46317 then --"C";45761;46317;556
 retval=retval || 'C';
 elsif w between 45253 and 45760 then --"B";45253;45760;507
 retval=retval || 'B';
 elsif w between 46318 and 46825 then --"D";46318;46825;507
 retval=retval || 'D';
 elsif w between 47614 and 48118 then --"H";47614;48118;504
 retval=retval || 'H';
 elsif w between 50906 and 51386 then --"Q";50906;51386;480
 retval=retval || 'Q';
 elsif w between 52218 and 52697 then --"T";52218;52697;479
 retval=retval || 'T';
 elsif w between 49896 and 50370 then --"M";49896;50370;474
 retval=retval || 'M';
 elsif w between 47297 and 47613 then --"G";47297;47613;316
 retval=retval || 'G';
 elsif w between 47010 and 47296 then--"F";47010;47296;286
 retval=retval || 'F';
 elsif w between 50622 and 50905 then--"P";50622;50905;283
 retval=retval || 'P';
 elsif w between 52698 and 52979 then--"W";52698;52979;281
 retval=retval || 'W';
 elsif w between 49062 and 49323 then--"K";49062;49323;261
 retval=retval || 'K';
 elsif w between 50371 and 50613 then --"N";50371;50613;242
 retval=retval || 'N';
 elsif w between 46826 and 47009 then--"E";46826;47009;183
 retval=retval || 'E';
 elsif w between 51387 and 51445 then--"R";51387;51445;58
 retval=retval || 'R';
 elsif w between 45217 and 45252 then --"A";45217;45252;35
 retval=retval || 'A';
 elsif w between 50614 and 50621 then --"O";50614;50621;7
 retval=retval || 'O';
 end if;
 end;
 end if;
 s=substring(s,2,l-1);
 l=l-1;
end loop;
return retval;
end;
$BODY$
 LANGUAGE plpgsql IMMUTABLE;

使用方法

SELECT
 enumvalue,
 CnFirstChar(enumvalue) as firstChar 
FROM
 field_enum_list
WHERE
 tablename = 'equip_interplantpipeline'
AND filedname = 'pipenet'
ORDER BY
 firstChar

CASE WHEN

SELECT 
    CASE
    WHEN filename ~* 'F001' THEN
        2.4
    ELSE
        1.5
    END AS weight
    FROM
        equip_filemap
    

postgis 空间查询

修改空间字段类型 2019-4-25

    alter table underline alter geom type geometry(MultiLineString)
    
    alter table equip_riskmap alter COLUMN risklevel type int using risklevel::int

查询坐标点，空间信息转坐标点

select ST_AsText(geom)

两个几何要素是否相交

ST_Intersects(e.geom, r.geom)

计算两点间的欧式距离

SELECT st_distance(geometry('POINT(115.967054194395 39.7315610991521)')::geography,geometry('POINT(115.96694062267 39.731557951387)')::geography)

判断点是否在面内

ST_Contains(polygon.geom,point.geom)

判断两个几何对象是否是重叠

 ST_Overlaps(geometry, geometry)

判断两个几何对象是否互相穿过

ST_Crosses(geometry, geometry)

验证几何图形是否有效

ST_MakeValid(geom)

构建线

ST_LineFromText("linestring(115.99595273353 39.716924496395,115.99633092501 39.716964729531,115.99626655199 39.716835983498,115.9960600219 39.716846712334,115.99595273353 39.716924496395)")

构建多边形

ST_MPolyFromText("multipolygon(((115.99595273353 39.716924496395,115.99633092501 39.716964729531,115.99626655199 39.716835983498,115.9960600219 39.716846712334,115.99595273353 39.716924496395)))")

获取切割图形

st_split(ST_MakeValid(inputgeom),bable)--第一个参数为被切图行

获取中心点

ST_Centroid(geom)

获取相交部分

ST_Intersection(geom,geom)