字段转小写"/>
postgresql表名转小写,字段转小写
通过oracle转pg,表名变成了大写,字段也大写了,后台代码改太麻烦,所以需要转为小写。
-- 创建exec(sqlstring)函数方便执行
CREATE OR REPLACE FUNCTION "public"."exec"("sqlstring" varchar)RETURNS "pg_catalog"."varchar" AS $BODY$declareres varchar(50);BEGINEXECUTE sqlstring;RETURN 'ok';END
$BODY$LANGUAGE plpgsql VOLATILECOST 100-- 通过下列语句转换column_name中的大写字母
SELECTexec('alter table "' || table_name || '" rename column "' || column_name || '" to ' || lower( column_name ) || ';')
FROMinformation_schema.COLUMNS
WHEREtable_schema = 'public' AND column_name <> lower(column_name)--AND table_name like '%';-- 查询表名中的大写
SELECT * FROM information_schema.TABLES
WHERE table_schema = 'public' AND table_catalog = '表名' AND table_name <> lower( table_name );-- 修改表名中的大写为小写
SELECTexec ( 'alter table "' || table_name || '" rename to ' || lower( table_name ) || ';' )
FROMinformation_schema.tables
WHEREtable_schema='public' and table_catalog = '表名' and table_name <> lower(table_name);
亲测,很好用
更多推荐
postgresql表名转小写,字段转小写
发布评论