Renaming multiple columns in PostgreSQL

Question

My table has a bunch of columns in the following format:

_settingA _settingB _settingB 

And I want to rename them simply to add a prefix like this:

 _1_settingA _1_settingB _1_settingC 

What is the fastest / most efficient way to achieve this?

EDIT: I have to add that I have a lot more than three columns to rename in this way. If I only had three, I would just do it manually one by one. And thanks for the downward, whoever you are.

Answer 1

There is no single aproach command. Obviously, you could enter some commands for RENAME by yourself, but let me make some improvements :) As I said in this answer

... for all such bulk-admin operations, you can use PostgreSQL system tables to generate queries for you, rather than manually writing them

In your case, it will be:

 SELECT 'ALTER TABLE ' || tab_name || ' RENAME COLUMN ' || quote_ident(column_name) || ' TO ' || quote_ident( '_1' || column_name) || ';' FROM ( SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name, column_name FROM information_schema.columns WHERE table_schema = 'schema_name' AND table_name = 'table_name' AND column_name LIKE '\_%' ) sub; 

This will give you a set of strings that are SQL commands, such as:

 ALTER TABLE schema_name.table_name RENAME COLUMN "_settingA" TO "_1_settingA"; ALTER TABLE schema_name.table_name RENAME COLUMN "_settingB" TO "_1_settingB"; ... 

There is no need to use table_schema in WHERE if your table is in the public schema. Also remember, using the quote_ident() function - read my original answer for more explanation.

Edit:

I changed my query so that it now works for all columns with a name starting with an underscore _ . Since the underscore is a special character in SQL pattern matching, we must avoid it (using \ ) to find it.

Answer 2

Something simple how this will work.

 SELECT FORMAT( 'ALTER TABLE %I.%I.%I RENAME %I TO %I;', table_catalog, table_schema, table_name, column_name, '_PREFIX_' + column_name ) FROM information_schema.columns WHERE table_name = 'foo'; 

%I will do quote_ident , which is much better. If you are in PSQL, you can run it with \gexec

Answer 3

You cannot do this.

All actions except RENAME and SET SCHEMA can be combined into a list of several changes applied in parallel.

The most effective way is to use ActiveRecord.