PostgreSql的基本用法
- 日期转字符串特定格式
to_char(inputtime,'yyyy-MM-dd') as 别名
2.sql转义 比如 <
<![CDATA[]]>
3.取第一个不为空的字段
COALESCE("","") as "别名"
4.pgsql显示一个月的日期:
select to_char(tt.day, 'yyyy-mm-dd') as day , extract(DOW FROM cast(tt.day as TIMESTAMP)) as weekday ,'星期'||extract(DOW FROM cast(tt.day as TIMESTAMP))
from (select generate_series(cast(to_char(current_date, 'yyyy-mm') || '-01' as date),
cast(cast(to_char(current_date, 'yyyy-mm') || '-01' as timestamp) + '1 MONTH' + '-1 d' as
date), '1 d') as day) as tt
order by day;
5.pgsql截取年、月、日
extract(类型 from 日期)
6.pgsql生成自定义一串数generate_series(‘起始数’,‘结束数’,‘步长’)
select generate_series(100,110,1)
7.pgsql的case when用法
SELECT *
FROM iftest
order by id desc
OFFSET 1
limit 2
8.先转换类型再截取
left(cast(字段 as varchar),8) as 别名
9.pgsql的日期类型加减乘除运算
--日期直接与一个整型相加:
select cast('2019-09-01' as date) + interval '1 month' - interval '1 day'
select date '2019-09-01' + interval '2 years' + '1 months' + '2 days' +'2 hours'+'2 minutes' +'2 seconds'
--日期与时间间隔(interval)的运算:
select cast('2019-09-01' as date) + integer '7'
--日期与时间类型(time)的运算:
select date '2015-04-10' + time '3:00';
--时间戳类型与时间类型的运算:
select timestamp '2015-04-17 10:00:00' - time '03:00'
--时间间隔之间的运算:
select interval '1 hours' + interval '10 minutes'
--时间间隔与时间类型的运算:
select time '05:00' - interval '3 hours'
--时间间隔的乘法
select 900 * interval '1 second'
--时间间隔的除法
select interval '1 hour' / double precision '1.5'
10.pgsql字段截取
substring(字段 ::varchar(50) from 开始下标 for 截取长度) ::time
11.pgsql字符串动态截取
函数:trim([leading | trailing | both] [characters] from string)
说明:Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string 去除尽可能长开始,结束或者两边的某类字符,默认为去除空白字符,当然可以自己指定,可同时指定多个要删除的字符串
例子:trim(both 'x' from 'xTomxx') = Tom
12.pgsql字符串代替
函数:replace(string text, from text, to text)
说明:Replace all occurrences in string of substring from with substring to 将字符的某一子串替换成另一子串
例子:replace('abcdefabcdef', 'cd', 'XX') = abXXefabXXef
13.pgsql字符串前置自动补充
lpad(string text, length int [, fill text])
作用:对字符串左边进行某类字符自动填充,即不足某一长度,则在左边自动补上指定的字符串,直至达到指定长度,可同时指定多个自动填充的字符
例子:lpad('hi', 5, 'xy') = xyxhi
14.pgsql字符串后置自动补充
函数:rpad(string text, length int [, fill text])
说明:对字符串进行填充,填充内容为指定的字符串
例子:rpad('hi', 5, 'xy') = hixyx
15.pgsql字符串添加双引号
quote_ident(string text)
例子:quote_ident('Foo bar') = "Foo bar"
16.pgsql字符串添加单引号
quote_literal(string text)
例子:quote_literal('O\'Reilly') = 'O''Reilly'
17.pgsql字符串指定修改
translate(string text, from text, to text)
作用: 将字符串中某些匹配的字符替换成指定字符串,目标字符与源字符都可以同时指定多个
例子:translate('12345', '14', 'ax') = a23x5
18.pgsql截取字符串substr
函数:substr(string, from [, count])
说明:Extract substring (same as substring(string from from for count)) 截取子串
例子:substr('alphabet', 3, 2) = ph
19.pgsql截取字符串substring
函数:substring(string [from int] [for int]) 或者
substring(string, int[,int])
说明:截取任意长度的子字符串
例子:substring('Thomas' from 2 for 3) = hom
20.pgsql创建索引和查看当前表的所有索引和删除索引
CREATE INDEX 索引名 ON 表名 (字段);
例:CREATE INDEX orders_report_query_date_index ON orders_report USING btree ("query_date")
select * from pg_indexes where tablename='表名';
drop index 索引名
21.查看分析sql运行情况
explain ANALYZE
select * from orders_2019 where "工厂ID"='2000'
22.trunc的用法,截取指定小位数
作用:返回一个数截断到一定的小数位数。
例子:trunc(123.23,1) 输出:123.2
23.pgsql的to_char高级用法
解释:0是占位符(有则显示结果值,无则显示0),9是显示符(有则显示,无则不显示)
24.pgsql的update连表
Update t1
Set字段 = ‘***’(字段前不要加表名,因为自动就是修改t1表中字段)
From t2
Where t1.id = t2.id…………..
25.pgslq的union和union all
union:连接数据时自动去重
union all:连接时不会自动去重,效率比union高
26.pgsql触发器模板
CREATE OR REPLACE FUNCTION "public"."tri_last_date_ins"()
RETURNS "pg_catalog"."trigger" AS $BODY$
-- Routine body goes here...
declare cu integer ;
BEGIN
if TG_OP = 'INSERT' then
if EXISTS(select count(*) from last_date where "日期" = new."日期")
then delete from last_date where "日期" = new."日期";
end if;
end if ;
return new ;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- rollback ;
return null ;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
27.pgsql过程函数
CREATE OR REPLACE FUNCTION "public"."testpro"()
RETURNS SETOF "pg_catalog"."record" AS $BODY$
DECLARE
declare re INTEGER;
declare rs record;
BEGIN
create temp table temp_iftest(
id INTEGER,
sex INTEGER,
name VARCHAR,
age INTEGER,
birth DATE
)ON COMMIT DROP;
insert into temp_iftest
select id,sex,name,age,birth from iftest;
FOR rs in
select * from temp_iftest
LOOP
return next rs;
END LOOP;
--select max("id") into re from temp_iftest;
--return re;
--select max("id") into re from iftest;
--return re;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
调用(需要为返回的record指定返回的列名):
select * from "public"."testpro"() a(id integer,
sex integer,
name VARCHAR,
age integer,
birth DATE);
补充:过程函数如果需要把表名,字段当成参数传入函数,在里面需要使用
quote_ident()函数包着