postgresql 大小写的问题

PostgreSQL数据库对于大小写的处理规则比较特殊.

简要总结如下：
1.PG数据库是严格区分大小写的.
2.默认情况下，PG数据库会把所有的SQL语句都转化成小写然后再执行.
说明：加双引号的语句除外.
换句话说，如果想成功执行名称中带有大写字母的对象，则需要把对象名称加上双引号.

-- 创建exec(sqlstring)函数⽅便执⾏

CREATE OR REPLACE FUNCTION "public"."exec"("sqlstring" varchar)
RETURNS "pg_catalog"."varchar" AS $BODY$
declare
res varchar(50);
BEGIN
EXECUTE sqlstring;
RETURN 'ok';
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

 

-- 通过下列语句转换column_name中的⼤写字母

SELECT
exec('alter table "' || table_name || '" rename column "' || column_name || '" to ' || lower( column_name ) || ';')
FROM
information_schema.COLUMNS
WHERE
table_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 );

-- 修改表名中的⼤写为⼩写

SELECT * FROM information_schema.TABLES
WHERE table_schema = 'public' AND table_catalog = '表名' AND table_name <> lower( table_name );
-- 修改表名中的⼤写为⼩写
SELECT
exec ( 'alter table "' || table_name || '" rename to ' || lower( table_name ) || ';' )
FROM
information_schema.tables
WHERE
table_schema='public'
and table_catalog = '数据库名'
and table_name <> lower(table_name)