postgresql 数据库操作点记

普通查询查询结果拼接查询所有字段 排除某些字段处理时间查询结果去除null值安装扩展 自动填充uuid数据库插入guid函数清空表格数据分组查询的GroupBy添加字段 修改字段获取字段名、类型、注释、是否为空:数据库创建删除导入导出postgresql 查询批更新postgresql 查询批量插入postgresql 字段类型查询 转换postgresql删除活动链接的数据库 ***-4-28***匹配操作设置自增主键查询不区分大小写，去除重复记录，分组创建gin索引postgresql 获取汉字首字母函数CASE WHEN [postgis 空间查询](/docs)修改空间字段类型 ***-4-25***查询坐标点，空间信息转坐标点两个几何要素是否相交计算两点间的欧式距离判断点是否在面内判断两个几何对象是否是重叠判断两个几何对象是否互相穿过验证几何图形是否有效构建线构建多边形获取切割图形获取中心点获取相交部分

普通查询

查询结果拼接

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_nameFROM information_schema.columns As cWHERE 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

处理时间

/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-osspSCHEMA publicVERSION "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('-10-10' as char),CAST('e10adc3949ba59abbe56e057f20f883e' as uuid);

postgresql删除活动链接的数据库-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已建好 主键为idCREATE SEQUENCE public_pdfsearch_id_seqSTART WITH 1INCREMENT BY 1NO MINVALUENO MAXVALUECACHE 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$declareretval character varying;c character varying;l integer;b bytea; w integer;beginl=length(s);retval='';while l>0 loopc=left(s,1);b=convert_to(c,'GB18030')::bytea;if get_byte(b,0)<127 thenretval=retval || upper(c);elsif length(b)=2 thenbeginw=get_byte(b,0)*256+get_byte(b,1);--汉字GBK编码按拼音排序，按字符数来查找，基于概率来说，效率应该比挨个强:)if w between 48119 and 49061 then --"J";48119;49061;942retval=retval || 'J';elsif w between 54481 and 55289 then --"Z";54481;55289;808retval=retval || 'Z';elsif w between 53689 and 54480 then --"Y";53689;54480;791retval=retval || 'Y';elsif w between 51446 and 52208 then --"S";51446;52208;762retval=retval || 'S';elsif w between 52980 and 53640 then --"X";52980;53640;660retval=retval || 'X';elsif w between 49324 and 49895 then --"L";49324;49895;571retval=retval || 'L';elsif w between 45761 and 46317 then --"C";45761;46317;556retval=retval || 'C';elsif w between 45253 and 45760 then --"B";45253;45760;507retval=retval || 'B';elsif w between 46318 and 46825 then --"D";46318;46825;507retval=retval || 'D';elsif w between 47614 and 48118 then --"H";47614;48118;504retval=retval || 'H';elsif w between 50906 and 51386 then --"Q";50906;51386;480retval=retval || 'Q';elsif w between 52218 and 52697 then --"T";52218;52697;479retval=retval || 'T';elsif w between 49896 and 50370 then --"M";49896;50370;474retval=retval || 'M';elsif w between 47297 and 47613 then --"G";47297;47613;316retval=retval || 'G';elsif w between 47010 and 47296 then--"F";47010;47296;286retval=retval || 'F';elsif w between 50622 and 50905 then--"P";50622;50905;283retval=retval || 'P';elsif w between 52698 and 52979 then--"W";52698;52979;281retval=retval || 'W';elsif w between 49062 and 49323 then--"K";49062;49323;261retval=retval || 'K';elsif w between 50371 and 50613 then --"N";50371;50613;242retval=retval || 'N';elsif w between 46826 and 47009 then--"E";46826;47009;183retval=retval || 'E';elsif w between 51387 and 51445 then--"R";51387;51445;58retval=retval || 'R';elsif w between 45217 and 45252 then --"A";45217;45252;35retval=retval || 'A';elsif w between 50614 and 50621 then --"O";50614;50621;7retval=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;

使用方法

SELECTenumvalue,CnFirstChar(enumvalue) as firstChar FROMfield_enum_listWHEREtablename = 'equip_interplantpipeline'AND filedname = 'pipenet'ORDER BYfirstChar

CASE WHEN

SELECT CASEWHEN filename ~* 'F001' THEN2.4ELSE1.5END AS weightFROMequip_filemap

postgis 空间查询

修改空间字段类型-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)