Geek Answers Handbook

Renaming mixed-case tables in a single query

My database PostgreSQL 9.2has many tables named with mixed cases, like

Tbl_Sales, Tbl_Purch,Tbl_logMnth

What I want to do is

 alter table "Table1" rename to table1

but how easy is it to rename all mixed case tables in my database?

+4
source share
3 answers

Use the following selection to get the table (s) with mixed cases in the name

 SELECT  table_name ucase,lower(table_name) lcase
 FROM    information_schema.tables 
 where   table_type = 'BASE TABLE' and 
         table_schema = 'public' and 
         table_name ~ E'^[[:upper:]][^[:upper:]]'

PostgreSQL string function below and INFORMATION_SCHEMA.TABLES

and use PL / PGSQL SQL - DO to rename all tables with a mixed flag

do
$$
declare 
rw record;
begin
for rw in 
SELECT 'ALTER TABLE  "'||t.ucase||'" RENAME to '||t.lcase||';'  execme from (
SELECT  table_name ucase, lower(table_name) lcase
FROM    information_schema.tables 
where   table_type = 'BASE TABLE' and 
        table_schema = 'public' and 
        table_name ~ E'^[[:upper:]][^[:upper:]]')t
loop
execute rw.execme ;
end loop;
end;
$$
+2
source

ALTER TABLE     RENAME TO new_name

+3

SQL, , Python , pg_tables catalog.

, ( , , ), pg_ , , Postgres) , . , ALTER TABLE, , , COMMIT .

sqlfiddle .

, foo, .

, , public , . , pg_catalog information_schema. tableowner - , , , , -, postgres.

, . foo , foo, ERROR: relation "foo" already exists.

0

More articles:


All Articles