PgSql 知识库

--分页offset 和limit 位置可以互换 不影响结果
select * from abc.t_student offset 0 limit 2;
select * from abc.t_student limit 2 offset 0;

--表明加别名 用as 不用as 都可以
select * from abc.t_student as a  where a.id<23 limit 2 offset 0;
select * from abc.t_student  a  where a.id<23 limit 2 offset 0;

--列明别名用as 和不用as 都可以 且都是小写
select a.id as D,a.address dizhi from abc.t_student a where a.id<33
and address='beijing'
limit 10;

--查看执行计划
explain
select a.id as D,a.address dizhi from abc.t_student a where
a.id>42
and address like '%张%'
limit 10 offset 0;
--创建索引
create index index_address on abc.t_student(address);

-- 查看索引 直接表名 不用加schemas
select * from pg_indexes where tablename='t_student';

-- 删除索引 必须有schemas
drop index abc.i_address;

pgsql可以在Btree索引上指定操作符：text_pattern_ops、varchar_pattern_ops和 bpchar_pattern_ops，它们分别对应字段类型text、varchar和 char，官方解释为“它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时，被使用在涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询中

--创建索引
create index index_address on abc.t_student(address varchar_pattern_ops);

--再查执行计划就会走索引
explain
select a.id as D,a.address dizhi from abc.t_student a where
a.id>42
and address like '张%'
limit 10 offset 0;


-- 年
select to_char(to_timestamp(1608018517000 / 1000),'yyyy');
-- 年-月
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM');
-- 年-月-日
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd');
-- 年-月-日 时
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24'); 对应JAVA 的“yyyy-MM-dd HH”
-- 年-月-日 时:分
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24:MI');对应JAVA 的“yyyy-MM-dd HH:mm”
-- 年-月-日 时:分:秒
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24:MI:SS');对应JAVA 的“yyyy-MM-dd HH:mm:ss”

--查询某一天日期 不走索引（字段带函数）
explain
select * from abc.t_student where
to_char(create_time,'yyyy-MM-dd')='2022-08-22';
--当前时间加一天
SELECT now()::timestamp + '1 day';
--某天字符串加一天
SELECT to_timestamp('20220823','yyyyMMdd') + '1 day';
--查询某一天走索引，小于明天大于今天

--查询某一天走索引，小于明天大于等于今天
explain
select * from abc.t_student where
create_time <(to_timestamp('20220822','yyyyMMdd') + '1 day') and
create_time >=to_timestamp('20220822','yyyyMMdd');