Postgresql中的表名和字段名最好用小寫字母蹋肮，如果有大寫字母，一定要用雙引號。字符串值用單引號替蔬。
1、導入csv格式的數(shù)據(jù)到postgresql中：
先把數(shù)據(jù)整理成csv格式的屎暇，注意編碼為UTF-8承桥，字段之間用逗號隔開，csv文件要帶字段名根悼，或在pdadmin中點擊工具欄“執(zhí)行任意的SQL查詢”凶异，輸入：
copy cun_name(xzqdm,xzqmc,cm,jd,wd)
from 'E:\project\jzfp\zll_cun.csv'
with(format csv,header true,quote '"',delimiter ',',encoding 'UTF-8');
2、兩表通過公共字段聯(lián)合更新
UPDATE public.xian
SET code = public."DMSJ"."Code"
from public."DMSJ"
where xian.xm = "DMSJ"."Name";
update cun set "xianId" = (select id from xian) where cun."xianName" = xian.name
3挤巡、postgresql中的geometry字段類型轉(zhuǎn)化為字符串類型
SELECT ST_AsText("Center") from public."DMSJ" ;
4剩彬、將一張表的數(shù)據(jù)賦給另一張表
INSERT INTO public.dmsj(code, wz, center, name, fullname)
select public."DMSJ"."Code", ST_AsText(public."DMSJ"."WZ"), ST_AsText(public."DMSJ"."Center"),
public."DMSJ"."Name", public."DMSJ"."FullName" from public."DMSJ";
5、查詢某個字符的位置
SELECT position('.' in "fullname")
FROM public.dmsj
where code = '310110';
6矿卑、字符串轉(zhuǎn)數(shù)組
SELECT string_to_array("fullname",'.')
FROM public.dmsj
where code = '310110';
7喉恋、字段按照指定的字符進行分割，并返回指定位置的子字符結(jié)果
SELECT split_part("fullname",'.',1)
FROM public.dmsj
where code = '530629';
8、刪除null值
DELETE FROM public.dmsj
WHERE "isPK" is null;
9轻黑、查詢某個字段并去除重復值：
SELECT distinct sheng FROM public.dmsj;
10糊肤、通過兩張表的公共字段，用一張表的字段更新另一張表的字段
update shi set "shengId" = (select id from sheng where sheng.name = shi."shengName" )
update shi set "shengId" = (select id from sheng where sheng.name = shi."shengName" limit 1)
11苔悦、空間包含查詢
select t.* from "GFGX_Y_DMK_DMSJ" t inner join "DMSJ" h
on ST_Contains(ST_GeomFromText(ST_astext(h. "WZ")),ST_GeomFromText(ST_astext(t."WZ")))
where h."Code" = '530621' limit 20
