postgresql 案例分享

postgresql sql处理时间 timestamp to string

参考文档 timestame to string 的特定格式

在碰到时间需要处理成字符串的时候可以用这个去解决不能转换成json文档的问题。可以直接在sql层面改成sql 去解决问题。

格式

to_char(`timestamp`, `text`)

范例：

SELECT
	uuid AS mes_uuid,
	title,
	message,
	target_users,
	ms_type,
	to_char(build_time,'YYYY-MM-DD HH12:MI:SS') as build_time,
	status 
FROM
	messages 
WHERE
	ms_type IN ( 'sys_bc', 'sys_trg' ) 
	AND status IS NOT NULL
	ORDER BY build_time DESC;

postgresql Left join 联表查询

问题，当时遇到了一个问题：

我想在这里直接查出学校的名称，但是如果联表用inner join 的话 只能匹配到so_uuid存在的学校内容了

解决方法使用 left join

SELECT
	ms.uuid AS mes_uuid,
	ms.so_uuid,
	ms.build_time,
	so.name AS school_name,
	so.logo_uuid,
	ms.title,
	ms.message,
	ms.target_users,
	ms.ms_type,
	ms.status
FROM
	messages AS ms
    LEFT JOIN school_org AS so ON ( so.uuid = ms.so_uuid )
WHERE
	( ms.ms_type = 'sys_bc' AND ms.status = 'normal') 
	OR ( ms.status = 'normal' AND ms.ms_type = 'sch_bc' AND ms.so_uuid = 'SLOIQLMFX0ECI9R1GJRZCDCA1SW6BFFH' ) 
	OR ( ms.ms_type in ('sch_trg','sys_trg') AND ms.status = 'normal' AND ms.target_users ? '大王' )
	ORDER BY ms.build_time desc;

postgresql 匹配列表中的内容

数据结构为：

-- create table rabbits (rabbit_id bigserial primary key, info jsonb not null);
-- insert into rabbits (info) values
--   ('{"name":"Henry", "food":["lettuce","carrots"]}'),
--   ('{"name":"Herald","food":["carrots","zucchini"]}'),
--   ('{"name":"Helen", "food":["lettuce","cheese"]}');

-- select info->>'name' AS name from rabbits where (info->'food')::jsonb ? 'carrots';
-- 
-- -- 自己写的示例 
-- -- where target_users ? '小王'

-- alter table rabbits alter info type jsonb using info::jsonb;
-- create index on rabbits using gin ((info->'food'));
-- select info->>'name' from rabbits where info->'food' ? 'carrots';
-- 直接就是
where target_users ? '小王'

jsonb格式 通过->>, ->获取需要的值

一般使用->>来获取数据，没有""双引号。

查询jsonb中的值：

SELECT
	users.username,
	users.real_name,
	users.ext_info ->> 'base_infor' as base_info
FROM
	users
WHERE
	users.status = 'normal' 
	AND users.ext_info -> 'ischeck' @> '"1"';