postgresql的一些使用技巧

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, 120.161), 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,
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 

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