1、查询位置与某一点的距离（30.185,120.161） select earth_distance(ll_to_earth(30.185,120.161),ll_to_earth(latitude,longitude)) as distance from t_org_location order by distance asc； 2、查询某一位置（30.185,120.161）周边（6000）米以内的位置 SELECT * FROM t_org_location WHERE earth_Box(ll_to_earth(30.185,5000.00) @> ll_to_earth(latitude,longitude); 3、导出sql语句 pg_dump -h localhost -U postgres huihe -s>/home/felix/huihe.sql; -s 为不带数据 4、从sql导入数据表到数据库 > psql -h localhost -d databaseName -U username -f filename 5、多行合并问题 SELECT a.id,array_to_string(array( SELECT b.name FROM db_xx."B" b WHERE b.id=a.id ),',') as name from db_xx."A" a order by a.id 6.数据库地理信息问题解决 create extension cube; create extension earthdistance; postgresql-contrib-9.1 先安装这个，才有需要的controller文件 sudo apt-cache search postgresql-contrib-9.1 sudo apt-get install postgresql-contrib-9.1 7、case when 语法 select name,md5(indvl_id_nbr) as indvl_id_nbr,case when char_length(indvl_id_nbr)=18 or char_length(indvl_id_nbr)=15 then substring(indvl_id_nbr from 1 for 6) end as id_prefix,case when char_length(indvl_id_nbr)=18 then substring(indvl_id_nbr from 7 for 8) when char_length(indvl_id_nbr)=15 then '19' || substring(indvl_id_nbr from 7 for 6) else null end as id_brth_dt from myschema.table_test; 8、数组操作 1）数组插入 insert into t_test(labels) values('{{"test"},{"hello"},{"god"}}') 2)数组查询 是否包含某个字符串 select * from t_test where 'god' = ANY(labels) 3)删除操作 select array_remove(array[1,2,3,4],1); 4）数组添加操作 select ARRAY[4,5,6] || 7; 9、数据库字段多重意义判断问题 select a.*,case a.type when 1 then b.name when 2 then c.full_name end as memname from t_member a left join t_user_base b on a.mem_id=b.id left join t_org_base c on a.mem_id=c.id CASE WHEN D.wk_ptn_cd IS NULL THEN '00' ELSE D.wk_ptn_cd END AS wk_ptn_cd 10、数据库多记录合并问题 SELECT a.id,') as name from db_xx."A" a order by a.id 11、查询一个数组中是否包含特定字段，包含即显示为1 select id,case 340=ANY(praisers_user) when true then 1 end as cot from t_contact_share order by id 12、数据库进行group by的时候字符串的拼接方式：array_to_string(ARRAY(SELECT unnest(array_agg(abstracts))),')\ 13、数据库sum加和时，如果没有记录会显示为null，为了让其显示为0，用COALESCE(sum(c.amount),0) 14、数据库的起停 postgresql 9.1 服务停止 su postgres /usr/lib/postgresql/9.1/bin /pg_ctl -D /var/lib/postgresql/9.1/main stop -m fast 启动服务 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf postgresql 9.4 启动服务 su postgres /opt/postgresql/postgresql-9.4.1/bin/postgres -D /opt/postgresql/postgresql-9.4.1/data/ start server /opt/postgresql-9.4.1/bin/postgres -D /opt/postgresql-9.4.1/data/ or /opt/postgresql-9.4.1/bin/pg_ctl -D /opt/postgresql-9.4.1/data/ -l logfile start