postgresql数据库相关语句

通过sql语句查询postgresql的数据库剩余空间：

SELECT pg_size_pretty(pg_database_size('copotematch'));

通过sql语句查询postgresql的数据库中表的大小：

SELECT pg_size_pretty(pg_total_relation_size('tablename'));

插入空间坐标字段为geometry类型：

ALTER TABLE data_gis_jcdz_xxd_4301 ADD COLUMN geom geometry ( 'POINT', 4326 );

修改与查询geometry类型的SRID

SELECT UpdateGeometrySRID('xyd_to_gis_lgq', 'geom', 4326);
SELECT st_srid(geom) FROM xyd_to_gis_lgq limit 1;

将jd和wd字段赋值到空间坐标中

UPDATE data_gis_jcdz_xxd_4301 
SET geom = st_geomfromtext ( 'POINT(' || jd || ' ' || wd || ')' );

设置、查询自增长序列：

--创建自增长序列
CREATE SEQUENCE pg_addr_modify_dzid_seq START WITH 100000000000 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
--将字段设置自增长序列
ALTER TABLE data_gis_jcdz_xxd_4301 ALTER column dzid 
SET DEFAULT nextval('pg_addr_modify_dzid_seq');
--查询序列
SELECT nextval( 'pg_addr_modify_dzid_seq' );
--重置序列
ALTER SEQUENCE pg_addr_modify_dzid_seq RESTART WITH 100000000000;

随机抽取数据：

--随机抽取数据
SELECT * FROM data_gis_jcdz_xxd_4301 ORDER BY random( ) LIMIT 1000;

索引相关语句：

--创建索引：data_gis_jcdz_xxd_4401_dzid_id为索引名，data_gis_jcdz_xxd_4301表名，dzid为字段
CREATE INDEX data_gis_jcdz_xxd_4401_dzid_idx ON data_gis_jcdz_xxd_4301(dzid);
--查询索引：data_gis_jcdz_xxd_4301为表名
SELECT * FROM pg_indexes WHERE tablename = 'data_gis_jcdz_xxd_4301';

删除重复数据：

--方式1，最基础的形式，效率较低
delete from xyd_to_gis_4401_05 a where a.seq <> (select min(t.seq) from xyd_to_gis_4401_05 t where a.amc_address=t.amc_address);
--方式2，通过group by的方式
delete from xyd_to_gis_4401_05 a where a.seq not in (select min(seq) from xyd_to_gis_4401_05 group by amc_address);
--方式3，效率较高
delete from xyd_to_gis_4401_05 a where a.seq = any(array (select seq from (select row_number() over (partition by amc_address), seq from xyd_to_gis_4401_05) t where t.row_number > 1));