PostgreSql的基本用法

PostgreSql的基本用法

1. 日期转字符串特定格式

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()函数包着