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 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?

https://stackoverflow.com/a/49520887/124486 – Evan Carroll Mar 27 '18 at 19:20 — Evan Carroll, Mar 27 '18 at 19:20

score 9 · Accepted Answer · edited May 23 '17 at 11:59

9

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.

edited May 23 '17 at 11:59

Community

1
1

answered Oct 29 '15 at 09:45

Gabriel's Messanger

3,213
17
31

Regarding the following part: `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 `_`? – drmrbrewer Oct 29 '15 at 16:22
Yes, just change `AND column_name IN ('_settingA', '_settingB', '_settingC')` into `AND column_name LIKE '\\_%'`. I will edit my answer. – Gabriel's Messanger Oct 29 '15 at 19:26
Can't quite get this to work. I'm replacing `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. – drmrbrewer Oct 30 '15 at 08:14
I am sorry i'd correct the query. You were righ. Firstly `;` 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 `\\`. – Gabriel's Messanger Oct 30 '15 at 08:47
OK, great. I understand a bit more about your method: executing this does not actually update the column names, but instead generates the queries required to do so. That's clever, and it works. One suggestion for improvement: add `|| ';'` 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 `';'`) – drmrbrewer Oct 30 '15 at 09:57

score 0 · Answer 2 · answered Mar 27 '18 at 19:20

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

score 0 · Answer 3 · answered Apr 08 '20 at 14:13

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,

score -1 · Answer 4 · answered Oct 28 '15 at 21:04

-1

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.

answered Oct 28 '15 at 21:04

Ade Yahya

717
5
6

Renaming multiple columns in PostgreSQL

4 Answers4

Linked