【SQL】常用sql

1. 【Postgresql】分隔字符串Split

regexp_split_to_array(layer_name,'_') 

layer_name	store_id
1_layer_storeId1	null
2_layer_storeId2	null
3_layer_storeId3	null

按_分隔layer_name获取store_id设置给store_id列，注意数组下标从1开始

UPDATE meta_layer set store_id = (select arr[3] from regexp_split_to_array(layer_name,'_') arr)

layer_name	store_id
1_layer_storeId1	storeId1
2_layer_storeId2	storeId2
3_layer_storeId3	storeId3

2. 【Postgresql】2个字段中返回非null值

COALESCE(alias_name,name)

name	alias_name
电脑	null
手机	Xiao Mi 11 Ultra

要实现当alias_name = null 时查询结果是name，否则查询的是alias_name

select COALESCE(alias_name,name) as name from t_tb

name
电脑
Xiao Mi 11 Ultra

3. 【Postgresql】表结构

SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = '7365b8f1a0924e77909c6179799b7d61'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;