Postgres数据库笔记

设置临时变量

set session “unit.name” to “张三”;

select current_setting(‘unit.name’)

更多:https://www.jb51.net/article/204214.htm

根据父级id,查询子级

with recursive cte as(select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a where a.atid='5cbd1b00-ca0f-461c-9f13-2215bdaf1d9c'union all select k.atid,cast(c.name||'>'||k.name as varchar(200)) as name  from app_aqxj_equipment k inner join cte c on c.atid = k.parent_id--可以根据条件筛选where  k.type_id <> 'xunjianxiang')select atid,name from cte ;create or replace function func_get_loginUnit(in_userid varchar(200),out o_area text)
returns text as $$
DECLARE
 v_rec_record RECORD;
BEGIN
 o_area = '';
 FOR v_rec_record IN (WITH RECURSIVE r AS (
 SELECT userid,usertitle,userparentid,userwbscode FROM ta_users WHERE userid = in_userid
 union ALL
 SELECT ta_users.userid,ta_users.usertitle,ta_users.userparentid,ta_users.userwbscode FROM ta_users, r 
	WHERE ta_users.userid = r.userparentid and ta_users.userparentid <> '-1'
 )
SELECT userid,concat_ws(' > ',usertitle,'') as name,userparentid,userwbscode FROM r order by userwbscode asc) LOOP
 o_area := o_area || v_rec_record.name;
 
 END LOOP;
 return;
END;
$$
language plpgsql;

根据父级查询子级

with recursive cte as
(
select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a 
where a.atid='036bc777-c718-4a81-bbc7-b8fe6f8ce913'
union all 
select k.atid,cast(c.name||'>'||k.name as varchar(200)) as name  from app_aqxj_equipment k 
inner join cte c on c.atid = k.parent_id
where  k.type_id <> 'xunjianxiang'
)select atid,name from cte

根据子级查询父级

WITH RECURSIVE dict AS (
     SELECT *
     FROM app_aqxj_equipment
     WHERE atid= '036bc777-c718-4a81-bbc7-b8fe6f8ce913'
     union ALL
     SELECT app_aqxj_equipment.*
     FROM app_aqxj_equipment,
          dict
     WHERE app_aqxj_equipment.atid = dict.parent_id
)
SELECT atid AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name

从父级到子级

WITH RECURSIVE dict AS (
     SELECT *
     FROM app_aqxj_equipment
     WHERE atid= '036bc777-c718-4a81-bbc7-b8fe6f8ce913'
     union ALL
     SELECT app_aqxj_equipment.*
     FROM app_aqxj_equipment,
          dict
     WHERE app_aqxj_equipment.parent_id = dict.atid
)
SELECT atid AS id, name as name, parent_id as parentId
FROM dict
ORDER BY name

方法函数

SUBSTRING('abcd',2); -- result:bcd 表示从下标从2开始截取到末尾
SUBSTRING('abcd',1,2); -- result:ab 表示从下标从1开始,截取2个字符
to_char(date_trunc('day',now()),'yyyy-mm-dd') --时间格式转换为字符日期类型
to_date('2020-09-09','yyyy-mm-dd')) --把字符转换为日期格式 没有时分秒
to_timestamp('2020-09-09 22:22:00','yyyy-MM-dd hh24:mi:ss') --有时分秒
to_number(12,'99') --把字符转为数值型
split_part('2020-09-08','-',2) --分割字符,获取的是月,也就是09
concat_ws(':','1','2') --查询字段拼接 结果:1:2
string_agg(name,','); --多行合成一列,例如:张三,李四,王五,赵六

regexp_split_to_array('1,2,3',',') --返回数组格式
regexp_split_to_table('1,2,3',',') --拆分返回行模式

select case when 2>1 then '大于' else '小于' end --if判断

|| --字符拼接符

--其他类型格式转换
'1'::varchar
'1'::interger
'2020-01-01'::date

--当所有数据id全部一致时,没有唯一标识,使用下面这个函数可以查看隐藏的id
select ctid,* from wx_info limit 5
delete from wx_info where ctid in (select min(ctid) from wx_info where id = 1);



--查询行号
select 
row_number() over(order by recid) row_number
from jhrwbzs

创建游标

实用代码

实现夸库查询

--创建扩展
create extension
dblink;--dblink函数实现夸库查询

select * from dblink(
	'host=127.0.0.1 dbname=数据库名字 user=postgres password=postgres',
	'select atid,name from app_aqxj_place_info') as t
	(atid varchar(200),name varchar(200));

数学函数

函数 返回类型 描述 例子 结果
abs(x) 绝对值 abs(-17.4) 17.4
cbrt(double) 立方根 cbrt(27.0) 3
ceil(double/numeric) 不小于参数的最小的整数 ceil(-42.8) -42
degrees(double) 把弧度转为角度 degrees(0.5) 28.6478897565412
exp(double/numeric) 自然指数 exp(1.0) 2.71828182845905
floor(double/numeric) 不大于参数的最大整数 floor(-42.8) -43
ln(double/numeric) 自然对数 ln(2.0) 0.693147180559945
log(double/numeric) 10为底的对数 log(100.0) 2
log(b numeric,x numeric) numeric 指定底数的对数 log(2.0, 64.0) 6.0000000000
mod(y, x) 取余数 mod(9,4) 1
pi() double “π”常量 pi() 3.14159265358979
power(a double, b double) double 求a的b次幂 power(9.0, 3.0) 729
power(a numeric, b numeric) numeric 求a的b次幂 power(9.0, 3.0) 729
radians(double) double 把角度转为弧度 radians(45.0) 0.785398163397448
random() double 0.0到1.0之间的随机数值 random()
round(double/numeric) 圆整为最接近的整数 round(42.4) 42
round(v numeric, s int) numeric 圆整为s位小数数字 round(42.438,2) 42.44
sign(double/numeric) 参数的符号(-1,0,+1) sign(-8.4) -1
sqrt(double/numeric) 平方根 sqrt(2.0) 1.4142135623731
trunc(double/numeric) 截断(向零靠近) trunc(42.8) 42
trunc(v numeric, s int) numeric 截断为s小数位置的数字 trunc(42.438,2) 42.43

字符串函数和操作符

函数 返回类型 描述 例子 结果
string 丨丨 string text 字串连接 ‘Post’ 丨丨 ‘greSQL’ PostgreSQL
bit_length(string) int 字串里二进制位的个数 bit_length(‘jose’) 32
char_length(string) int 字串中的字符个数 char_length(‘jose’) 4
convert(string using conversion_name) text 使用指定的转换名字改变编码。 convert(‘PostgreSQL’ using iso_8859_1_to_utf8) ‘PostgreSQL’
lower(string) text 把字串转化为小写 lower(‘TOM’) tom
octet_length(string) int 字串中的字节数 octet_length(‘jose’) 4
overlay(string placing string from int [for int]) text 替换子字串 overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4) Thomas
position(substring in string) int 指定的子字串的位置 position(‘om’ in ‘Thomas’) 3
substring(string [from int] [for int]) text 抽取子字串 substring(‘Thomas’ from 2 for 3) hom
substring(string from pattern) text 抽取匹配 POSIX 正则表达式的子字串 substring(‘Thomas’ from ‘…$’) mas
substring(string from pattern for escape) text 抽取匹配SQL正则表达式的子字串 substring(‘Thomas’ from ‘%#”o_a#”_’ for ‘#’) oma
trim([leading丨trailing 丨 both] [characters] from string) text 从字串string的开头/结尾/两边/ 删除只包含characters(默认是一个空白)的最长的字串 trim(both ‘x’ from ‘xTomxx’) Tom
upper(string) text 把字串转化为大写。 upper(‘tom’) TOM
ascii(text) int 参数第一个字符的ASCII码 ascii(‘x’) 120
btrim(string text [, characters text]) text 从string开头和结尾删除只包含在characters里(默认是空白)的字符的最长字串 btrim(‘xyxtrimyyx’,’xy’) trim
chr(int) text 给出ASCII码的字符 chr(65) A
convert(string text, [src_encoding name,] dest_encoding name) text 把字串转换为dest_encoding convert( ‘text_in_utf8’, ‘UTF8’, ‘LATIN1’) 以ISO 8859-1编码表示的text_in_utf8
initcap(text) text 把每个单词的第一个子母转为大写,其它的保留小写。单词是一系列字母数字组成的字符,用非字母数字分隔。 initcap(‘hi thomas’) Hi Thomas
length(string text) int string中字符的数目 length(‘jose’) 4
lpad(string text, length int [, fill text]) text 通过填充字符fill(默认为空白),把string填充为长度length。 如果string已经比length长则将其截断(在右边)。 lpad(‘hi’, 5, ‘xy’) xyxhi
ltrim(string text [, characters text]) text 从字串string的开头删除只包含characters(默认是一个空白)的最长的字串。 ltrim(‘zzzytrim’,’xyz’) trim
md5(string text) text 计算给出string的MD5散列,以十六进制返回结果。 md5(‘abc’)
repeat(string text, number int) text 重复string number次。 repeat(‘Pg’, 4) PgPgPgPg
replace(string text, from text, to text) text 把字串string里出现地所有子字串from替换成子字串to。 replace(‘abcdefabcdef’, ‘cd’, ‘XX’) abXXefabXXef
rpad(string text, length int [, fill text]) text 通过填充字符fill(默认为空白),把string填充为长度length。如果string已经比length长则将其截断。 rpad(‘hi’, 5, ‘xy’) hixyx
rtrim(string text [, character text]) text 从字串string的结尾删除只包含character(默认是个空白)的最长的字 rtrim(‘trimxxxx’,’x’) trim
split_part(string text, delimiter text, field int) text 根据delimiter分隔string返回生成的第field个子字串(1 Base)。 split_part(‘abc~@~def~@~ghi’, ‘~@~’, 2) def
strpos(string, substring) text 声明的子字串的位置。 strpos(‘high’,’ig’) 2
substr(string, from [, count]) text 抽取子字串。 substr(‘alphabet’, 3, 2) ph
to_ascii(text [, encoding]) text 把text从其它编码转换为ASCII。 to_ascii(‘Karel’) Karel
to_hex(number int/bigint) text 把number转换成其对应地十六进制表现形式。 to_hex(9223372036854775807) 7fffffffffffffff
translate(string text, from text, to text) text 把在string中包含的任何匹配from中的字符的字符转化为对应的在to中的字符。 translate(‘12345′, ’14’, ‘ax’) a23x5

类型转换相关函数

函数 返回类型 描述 实例
to_char(timestamp, text) text 将时间戳转换为字符串 to_char(current_timestamp, ‘HH12:MI:SS’)
to_char(interval, text) text 将时间间隔转换为字符串 to_char(interval ’15h 2m 12s’, ‘HH24:MI:SS’)
to_char(int, text) text 整型转换为字符串 to_char(125, ‘999’)
to_char(double precision, text) text 双精度转换为字符串 to_char(125.8::real, ‘999D9’)
to_char(numeric, text) text 数字转换为字符串 to_char(-125.8, ‘999D99S’)
to_date(text, text) date 字符串转换为日期 to_date(’05 Dec 2000′, ‘DD Mon YYYY’)
to_number(text, text) numeric 转换字符串为数字 to_number(‘12,454.8-‘, ’99G999D9S’)
to_timestamp(text, text) timestamp 转换为指定的时间格式 time zone convert string to time stamp to_timestamp(’05 Dec 2000′, ‘DD Mon YYYY’)
to_timestamp(double precision) timestamp 把UNIX纪元转换成时间戳

三角函数列表

函数 描述
acos(x) 反余弦
asin(x) 反正弦
atan(x) 反正切
atan2(x, y) 正切 y/x 的反函数
cos(x) 余弦
cot(x) 余切
sin(x) 正弦
tan(x) 正切

函数详细:https://www.runoob.com/postgresql/postgresql-functions.html

评论

  1. Tongyao
    Windows Chrome
    9月前
    2021-5-06 12:06:41

    with recursive cte as
    (
    select a.atid,cast(a.name as varchar(200)) from app_aqxj_equipment a
    where a.atid=’239972297dd742e28043bfdfa9eef264′
    union all
    select k.atid,cast(c.name||’>’||k.name as varchar(200)) as name from app_aqxj_equipment k
    inner join cte c on c.atid = k.parent_id
    where k.type_id <> ‘xunjianxiang’
    )select atid,name from cte
    子级调用父级,这样也可以做

    • Tongyao
      Windows Chrome
      7月前
      2021-6-18 15:39:44

      这是 父级查询所有子级的!!!

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
下一篇