Postgresql

Postgresql

postgresql relation “xxx” already exists

DROP TALBE XXX

类型转换

cast(character varying as numeric)
double precision

修改列类型

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer)

去重

-- 去重多个列
SELECT DISTINCT code,name from test_dist
-- 查询重复数据
select distinct id, count(*) from emp group by id having count(*) > 1
-- 查询要保留的数据
select ctid, * from emp where ctid in (select min(ctid) from emp group by id)
-- 删除重复数据
delete from emp where ctid not in (select min(ctid) from emp group by id);
-- 删除重复数据
delete from ship_mmsi where id in (select min(id) from ship_mmsi group by name_en having count(*) > 1)

链接其他数据库

create extension dblink;
select * from 
dblink('dbname=aaa host=localhost port=5432 user=postgres password=953598751'::text,'select * from pier_point'::text) t 
(gid integer,name character varying,geom geometry)

重置 id

alter sequence 表名_序列字段_seq restart with 1;
eg: alter sequence test_gid_seq restart with 70

GeoJson

SELECT row_to_json(fc) geojson 
            FROM ( SELECT 'FeatureCollection' AS type , array_to_json(array_agg(f)) AS features 
               FROM (SELECT 'Feature' AS type ,ST_AsGeoJSON(geom)::json as geometry 
                       ,(SELECT row_to_json(t) FROM (SELECT name,gid) AS t) AS properties 
                   FROM fence_anchor
                     where geom is not null 
               ) AS f 
            ) AS fc

主键存在在忽略

insert into ship_ais (mmsi,imo) values ('1','4124') ON conflict(mmsi) DO NOTHING;

主键存在则更新

insert into ship_ais (mmsi,imo) values ('1','1111') ON conflict(mmsi) DO UPDATE SET imo = '1111'