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 as follows:
_1_settingA
_1_settingB
_1_settingC
I have a lot more than three columns to rename in this way. If I had just three, I'd just do it manually one by one.
What is the quickest / most efficient way to achieve this?
There's no single command aproach. Obviously you could type multiple comands for RENAME by your self, but let me intoduce some improvement:) As I said in this answer
...for all such bulk-admin-operations you could use PostgreSQL system tables to generate queries for you instead of writing them by hand
In your case it would 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;
That'll give you set of strings which are SQL commands like:
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 no need using table_schema in WHERE clause if your table is in public schema. Also remember using function quote_ident() -- read my original answer for more explanation.
Edit:
I've change my query so now it works for all columns with name begining with underscore _. Because underscore is special character in SQL pattern matching, we must escape it (using \) to acctually find it.
AND column_name IN ('_settingA', '_settingB', '_settingC') is there a way to specify that generically rather than having to list each explicitly (there are a lot), so that it operates on any column name beginning with _?
AND column_name IN ('_settingA', '_settingB', '_settingC') into AND column_name LIKE '\\_%'. I will edit my answer.schema_name with public and table_name with test, since these match the table's schema and name. That gives me a syntax error which seems to be solved by removing the ; from the first SELECT. But then I get ERROR: subquery in FROM must have an alias / LINE 5: FROM ( / HINT: For example, FROM (SELECT ...) [AS] foo. I've tried to follow the hint, but still can't get a working solution.; after first SELECT shouldn't be there. Seccondly subquery must have an alias (which I added sub). There was also third mistake in escaping character in LIKE clause -- there must be only one `\`.|| ';' at the end of the first SELECT so that you can copy/paste the strings directly without further editing. (I can see that your original answer tried to include this but it was interpreted as a literal ; rather than as a string ';')Something simple like 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 substantially nicer. If you're in PSQL you can run it with \gexec
You can use the following function : (I use this to add prefix on tables wiches have more than 50 columns)
First create the function :
CREATE OR REPLACE FUNCTION rename_cols( schema_name_ text,table_name_ text, prefix varchar(4))
RETURNS bool AS
$BODY$
DECLARE
rec_selection record;
BEGIN
FOR rec_selection IN (
SELECT column_name FROM information_schema.columns WHERE table_schema = schema_name_ AND table_name = table_name_) LOOP
EXECUTE 'ALTER TABLE '||schema_name_||'.'||table_name_||' RENAME COLUMN "'|| rec_selection.column_name ||'" TO "'||prefix|| rec_selection.column_name ||'" ;';
END LOOP;
RETURN True;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Then execute function :
SELECT rename_cols('public','test','d');
Hope it will be usefull,
You can't do that.
All the actions except RENAME and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel.
most efficient way is using ActiveRecord.
