msssql to pgsql 修改记录

pgsql
1. 没有isnull 用 coalesce
2. 字符串拼接用 ||
3. 字符串类型和int类型不会自动转换(用作条件时)
4. 多行转一列 string_agg(distinct(字段名),'分隔符') distinct是为了去重可以不要
5. unnest(string_to_array (par_LoadingNos, ',')) //string_to_array 以 , 分隔字符串 unnest 把数据变为一列返回
6. 没有charindex,用strpos (原字符串,需要查找的)
7. 没有getdate() 用 LOCALTIMESTAMP(0) 代替 参数指秒以下取几位
8. 联表更新方法 update a set value = 'test' from b,c where a.b_id = b.id and b.c_id = c.id and a.key = 'test' and c.value = 'test';
9. 查询表结构　　

select a.attnum AS "序号",
c.relname AS "表名",
cast(obj_description(relfilenode,'pg_class') as varchar) AS "表名描述",
a.attname AS "列名",
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as "字段类型",
t.typname as "类型",
SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\((.*)\)') "长度",
d.description AS "备注"
from pg_class c 
inner join pg_attribute a on a.attrelid = c.oid  
inner join pg_type t on t.oid =a.atttypid
left join pg_description d on d.objoid=a.attrelid and d.objsubid=a.attnum
where  lower(c.relname )= '表名称'
and a.attnum>0 
ORDER BY c.relname DESC,a.attnum ASC    

 

10. 创建临时表 create TEMPORARY table 表名
11.条件写法: IF 表达试 THEN ELSIF 表达试 THEN ELSE END IF; // IF EXISTS() THEN ....
12. 1、增加一列ALTER TABLE table_name ADD column_name datatype;

      2、删除一列
           ALTER TABLE table_name DROP column_name;

     3、更改列的数据类型
           ALTER TABLE table_name ALTER column_name TYPE datatype;

    4、表的重命名
          ALTER TABLE table_name RENAME TO new_name;

    5、更改列的名字
          ALTER TABLE table_name RENAME column_name to new_column_name;

    6、字段的not null设置
          ALTER TABLE table_name ALTER column_name {SET|DROP} NOT NULL;

    7、给列添加default
         ALTER TABLE table_name ALTER column_name SET DEFAULT expression;

13. 添加自增并设置种子和步长
     ALTER TABLE 表名
     ALTER COLUMN 字段 ADD
     GENERATED ALWAYS AS IDENTITY ( INCREMENT 步长 START 启始值 MINVALUE 最小值 MAXVALUE 最大值 CACHE 1 ) 

14. 原文链接：https://blog.csdn.net/pg_hgdb/java/article/details/79483772

--查询主键名称
SELECT
    pg_constraint.conname AS pk_name
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
WHERE
    pg_class.relname = 'table_name'
AND pg_constraint.contype = 'p';
--查询主键的详细信息
SELECT
    pg_constraint.conname AS pk_name,
    pg_attribute.attname AS colname,
    pg_type.typname AS typename
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = pg_constraint.conkey [ 1 ]
INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE
    pg_class.relname = 'table_name'
AND pg_constraint.contype = 'p';
 

 15. 程序传参数时,参数的类型必须和实际类型匹配,如 '1' 和 1  .前者是无法插入值类型的列中的. 

13. LASTVAL() 方法类型于sqlserver里的@@IDENTITY
14. 去两边空格 trim()
15. 正则(https://www.cnblogs.com/qiyebao/p/4980648.html)
1. select * from user where email ~* '^[a-h]'
2. 'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
'abc' LIKE 'c' false

'abc' SIMILAR TO 'abc' true
'abc' SIMILAR TO 'a' false
'abc' SIMILAR TO '%(b|d)%' true
'abc' SIMILAR TO '(b|c)%' false

16. 设置自增列启始值
ALTER TABLE public.sc_users3
ALTER COLUMN userid ADD
GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 2000 MINVALUE 2000 MAXVALUE 9223372036854775807 CACHE 1 )

17. INNER JOIN LATERAL与CROSS APPLY
并且LEFT JOIN LATERAL相同OUTER APPLY

18. 创建索引 CREATE INDEX Form_ASN_QIndex ON Form_ASN (FormID,VendorID);

19. 分页查询 order by lastname limit 5 offset 0;(order by 不是必须的)

20. 输出文字 到输出界面(就是在数据库把信息输出到消息那里 :print ) raise notice 'My name is %, I am a %.', 'Lewis', 'coder';

21. json和jsonb 或 Array 里取值出来 请参考操作符

22. 插入自增列 insert into test (id, info) OVERRIDING SYSTEM VALUE values (1,'test');

23.生成GUID需要添加扩展. 以下为一种(还有其它的方式)
create extension "uuid-ossp" ;
select uuid_generate_v4()

24.执行动态sql
execute 'select count(*) from dictionarys where parentid=1'
execute 'select objectid,name from dictionarys where parentid=$1 order by parentid,sort' using $1;
execute 'select objectid,name from dictionarys where parentid=$1 order by parentid,sort' using $1 into v_xxx;