Postgresql中的表名和字段名最好用小写字母，如果有大写字母，一定要用双引号。字符串值用单引号。
1、导入csv格式的数据到postgresql中：
先把数据整理成csv格式的，注意编码为UTF-8，字段之间用逗号隔开，csv文件要带字段名，或在pdadmin中点击工具栏“执行任意的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、两表通过公共字段联合更新
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字段类型转化为字符串类型
SELECT ST_AsText("Center") from public."DMSJ" ;
4、将一张表的数据赋给另一张表
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、字符串转数组
SELECT string_to_array("fullname",'.')
FROM public.dmsj
where code = '310110';
7、字段按照指定的字符进行分割，并返回指定位置的子字符结果
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
