postgres加密解密pgcrypto

create extension pgcrypto;

 --增加，加密解密模块；

drop extension pgcrypto;

--删除，加密解密模块；

定义--encrypt(bytea, bytea, text)--
update bill_his_yun set yun_ip=encode(encrypt('202.99','p08','des') ,'hex') where yun_id=299904910298845184;


    select decrypt(yun_ip,'p08','des')  from bill_his_yun  where yun_id=299904910298845184;
    
    
    select convert_from(decrypt(decode(yun_ip,'hex'),'p08','des'),'SQL_ASCII') as yun_ip from bill_his_yun where yun_id=299904910298845184;
text加密方式

AES
DES/3DES/CAST5
Blowfish

查询表时一定符合类型

select nm, encrypt(cast(tb1.nm as bytea) ,'aa','aes') from tb1

 

select nm,test,
encrypt(cast(test as bytea) ,'aa','aes') as 加密, 
convert_from(decrypt(encrypt(cast(test as bytea),'aa','aes'),'aa','aes'),'SQL_ASCII') as 解密
from tb1

---------------------------------------------------------

select encrypt('123456年a','aa','aes');
--\000\003AXv\327\370\351\363\006\242\267A\245yX
 select convert_from(decrypt('\000\003AXv\327\370\351\363\006\242\267A\245yX','aa','aes'),'SQL_ASCII');
--123456年a 
select convert_from(decrypt(encrypt('123456年a','aa','aes'),'aa','aes'),'SQL_ASCII');
--123456年a 
—————