数据库Tips备忘录

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 相同的数据，保留最新的一条，删除其他的数据

delete from table_1 d
      where
          and exists (
          select from table_1  t
          where
              t.name = d.name
              and t.id  = d.id

              and t.ctid < d.ctid
    )

查看正在执行的 SQL

• PostgerSQL 11 之后可以使用 pg_stat_activity 查看统计信息
• 查询正在执行的 SQL， 并按照持续时间排序

select  query,state,pid, query_start,current_timestamp  - query_start AS runtime, usename, datname ,  application_name,client_addr , client_hostname , client_port
 from pg_stat_activity
  order by runtime

• 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);