过滤Unicode字符

sed -i 's/\u0000//g' json.txt

使用pgfutter

Github pgfutter

pgfutter --jsonb json 你的json文件

创建唯一索引

CREATE UNIQUE INDEX status_id_uniq  ON status ( (data->>'id') );

注意，->返回的是jsonb，->>返回的是text.

插入数据

导入的json对象是

{
"result":{
    "statuees":[
    {
    "id":111,
    "text":"data"
    }
    ]
}
}

然后只抽取 statuses 的来插入到另一张表里.即将数组元素拆分成每个元素（即每一条微博），然后导入到另一个表里，每一行代表一条微博.


insert into status select jsonb_array_elements(data->'result'->'statuses')   from import_status where jsonb_array_length(data->'result'->'statuses') > 0 ON CONFLICT DO NOTHING;

创建时间索引

create index status_created_at on status (((data->>'created_at')::timestamp with time zone));

ERROR:  42P17: functions in index expression must be marked IMMUTABLE
LOCATION:  ComputeIndexAttrs, indexcmds.c:1108
Time: 0.354 ms

use-timestamptz

解决办法:

CREATE OR REPLACE FUNCTION f_to_imts(text)
  RETURNS timestamptz AS
$$SELECT $1::timestamp with time zone $$
  LANGUAGE sql IMMUTABLE;


create index status_created_at on status ( f_to_imts(data->>'created_at') );