PostgreSQL, SQLite, PostGIS Tips 操作备忘录
PostgreSQL
常规
会话查询中设置时区为东八区
- SET TIME ZONE ‘Asia/Shanghai’
timestamptz 时间转字符串(年-月-日 时:分:秒)
- select to_char( time at timezone ‘PRC’, ‘YYYY-MM-DD HH24:MI:SS’)
- https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-to_char
jsonb 字段与主表里的字段合并
-
select json::jsonb || json_build_object(‘name’,col_name)::jsonb
-
select ‘{‘a’:1}’::jsonb || NULL json 对象与 null 合并会变成 null 并不保留 原来的 json 对象
- 解决:将 null 转为空 json 对象: || (case when jsonb_col is null then ‘{}’::jsonb else jsonb_col end)
json 字段查询
- select (json ->>‘value’)::float as value
in 查询
- in (1,2,3) <—> any([1,2,3] )
生成时间序列
- SELECT to_char(DAY,‘YYYY-MM-dd’) as dtime from generate_series(TIMESTAMP ‘2023-01-20’::timestamptz, ‘2023-01-25’::timestamptz, INTERVAL ‘1 day’) AS t(DAY)
- 20 号-25 号天间隔的时间序列
删除重复的数据
- 利用 postgresql 的 ctid (表示数据记录的物理行当信息,指的是 一条记录位于哪个数据块的哪个位移)
- 查找 table_1 表中 id 和 name 相同的数据,保留最新的一条,删除其他的数据
1 | delete from table_1 d |
查看正在执行的 SQL
- PostgerSQL 11 之后可以使用 pg_stat_activity 查看统计信息
- 查询正在执行的 SQL, 并按照持续时间排序
1 | select query,state,pid, query_start,current_timestamp - query_start AS runtime, usename, datname , application_name,client_addr , client_hostname , client_port |
- SELECT pg_cancel_backend(pid); : 取消执行中的 SQL
- SELECT pg_terminate_backend(pid); : 强行终止正在执行的 SQL
PostGIS
geometry 和 geography
- geometry 投影坐标系
- geography 地理坐标系:求 距离(米 面积,经纬度点相交等
GPS 坐标线段扩展为 10 米宽的图形
- ST_Buffer(st_setsrid(line,4326)::geography, 10)::geometry
求点到线的距离米
- select ST_Distance(ST_GeographyFromText(‘LINESTRING(116.415767 39.916042, 102.833963 24.916456)’),st_makepoint(121.452027, 31.242725)::geography)
GiST 加速索引
- CREATE INDEX tmp_table ON tmp_table USING GIST (gemo_col);
SQLite3
重命名索引
- DRO P INDEX old_index; CREATE INDEX new_index ON demo(cols);
json 字段查看
- json_extract(json_col, ‘$.value’)
datetime 转 Unix 时间戳
- UNIXEPOCH(datetime)
- STRFTIME(‘%s’, datetime);