pgsql常用函数
一、sql语句
DROP TABLE IF EXISTS "public"."student";
CREATE TABLE "public"."student" (
"id" int4 NOT NULL,
"name" varchar(255) COLLATE "pg_catalog"."default",
"grade" varchar(255) COLLATE "pg_catalog"."default",
"create_time" date
);
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO "public"."student" VALUES (1, ' xinglei ', '201212.31000', '2022-07-27');
INSERT INTO "public"."student" VALUES (2, ' xinglei ', '12123', '2022-07-28');
INSERT INTO "public"."student" VALUES (3, ' xinglei ', '12123', '2022-07-29');
-- ----------------------------
-- Primary Key structure for table student
-- ----------------------------
ALTER TABLE "public"."student" ADD CONSTRAINT "student_pkey" PRIMARY KEY ("id");
二、函数
-- 1、将字符串转换为指定精度的numeric类型数据
SELECT grade::numeric(10, 3) "numeric" from student
-- 2、字符串拼接
SELECT name from student
UNION SELECT name || '拼接字符串' from student
-- 3、计算给出string的MD5散列
SELECT name from student
UNION SELECT md5(name) from student
-- 4、去除左右两边的空格
SELECT ltrim(rtrim(name)) from student
-- 5、字串转化为大写
SELECT "upper"(name) from student
-- 6、字符串截取
SELECT substr(ltrim(rtrim(name)), 1,3) from student
-- 7、pgsql合并函数string_agg
SELECT ltrim(rtrim(name)),string_agg(distinct(grade), '#') "grade" from student
GROUP BY ltrim(rtrim(name))
-- 8、pgsql转数组
SELECT ltrim(rtrim(name)),"array_agg"(distinct(grade)) "grade" from student
GROUP BY ltrim(rtrim(name))
-- 9、取最新的一条记录（pgsql转数组函数应用）
SELECT ltrim(rtrim(name)),"array_agg"(grade order by create_time desc)::VARCHAR "grade" from student
GROUP BY ltrim(rtrim(name))
union
SELECT ltrim(rtrim(name)),("array_agg"(grade order by create_time desc))[1] "grade" from student
GROUP BY ltrim(rtrim(name))
--10、时间格式化
to_char(列名,‘yyyy-mm-dd hh24:mi:ss’)
--11、left、right字符串截取
SELECT "left"('Y043340123', 6),"right"('Y043340123', 6)
--12、position获取指定字符占位
SELECT "position"('Y043_340123', '_')
