pgsql json to text

正则
postgresql中使用正则表达式时需要使用关键字“~”

select * from user where email ~ '^[A-H]' --匹配email地址以A-H开头的记录  
select * from user where email ~* '^[a-h]' --匹配email地址以A-H和a-h开头的记录 

to json

SELECT '{"bar": "baz", "balance":      7.77, "active":false}'::json;
to text
SELECT * from batch_info where position('ADD' in stat_info::text) > 0;

alter

ALTER TABLE ad_rs_barrier add column upd_help varchar(1024); 
alter table ad_rs_barrier drop column if exists confidence_help;

json

SELECT
    json_array_length ( ( SELECT check_ids FROM ics_check_pkg WHERE process_id = 101 ) )

SELECT json_array_elements ( ( SELECT check_ids FROM ics_check_pkg WHERE process_id in ('2'))  ) 

SELECT to_char(to_timestamp(t.start_time / 1000), 'YYYY-MM-DD HH24:MI:SS') , t.* FROM ics_batch_info t ORDER BY t.start_time desc;

SELECT
    a.* 
FROM
    ( SELECT json_array_elements_text ( ( SELECT check_ids FROM ics_check_pkg WHERE process_id = 15 ) ) AS b ) AS a 
WHERE
    a.b IN ( 'AFOR000030')

time zone

select  now() at time zone 'Asia/Shanghai'
SET time zone  'Asia/Shanghai'
show time zone;

    insert into ics_check_pkg (pkg_type,process_id,process_name, pkg_name,check_ids)
    values (#{pkgType,jdbcType=VARCHAR},
    (SELECT max(process_id)+1 from ics_check_pkg),
    #{processName,jdbcType=VARCHAR},
    #{pkgName,jdbcType=VARCHAR},
    #{checkIds,jdbcType=OTHER})

DROP TABLE IF EXISTS "public"."ad_arrow";
CREATE TABLE "public"."ad_arrow" (
  "arr_id" int8 PRIMARY KEY NOT NULL,
  "arr_direct" varchar(20) NOT NULL DEFAULT '0',
  "confidence" varchar(1024) COLLATE "pg_catalog"."default",
  "upd_stat" varchar(512) COLLATE "pg_catalog"."default",
  "cur_task_id" int8 NOT NULL,
  "tile_id" int4,
  "source" varchar(250) COLLATE "pg_catalog"."default",
  "coll_time" varchar(100) COLLATE "pg_catalog"."default",
  "make_time" varchar(100) COLLATE "pg_catalog"."default"
);

ALTER TABLE public."ad_arrow" ADD COLUMN geom geometry(GeometryZ, 3857) NOT NULL;
CREATE INDEX arrow_idx_geom ON "ad_arrow" USING GIST (geom);
CREATE INDEX arrow_idx_cur_task_id ON "ad_arrow" (cur_task_id);
CREATE INDEX arrow_idx_arr_id ON "ad_arrow" (arr_id);

语法:unnest(anyarray)
返回值:setof anyelement(可以理解为一个(临时)表)
说明:unnest函数将输入的数组转换成一个表,这个表的每一列都代表相应的一个数组中的元素。
如果unnest与其他字段一起出现在select中，就相当于其他字段进行了一次join。
select '张三' as name, unnest(Array['语文','数学','英语']) as course;
 name | course
------+--------
 张三 | 语文
 张三 | 数学
 张三 | 英语
(3 rows)
SELECT "unnest"(array[1,2,3]) INTERSECT SELECT "unnest"(array[3,4,5])
3

select string_to_array(concat('''',array_to_string(string_to_array('2,0', ','),''','''),''''),',');
select address,poi_id from poi where poi_id in (select "unnest"(string_to_array('2,0,0,8', ',')));

PostgreSQL单列多行变一行&一行变多行
select id,array_agg(name)
AS NAME_NEW from test
group by 1
ORDER BY 1;

select id,array_to_string(array_agg(name),',')
AS NAME_NEW from test
group by 1
ORDER BY 1;
一行变多行
select id,regexp_split_to_table(name_new,',')
AS NAME from test;

select id,unnest(string_to_array(name_new,','))
AS NAME from test;

select 3,regexp_split_to_table(array_to_string(array_agg(array[1,2,3]),','),',');
3,1
3,2
3,3