Skip to content

Instantly share code, notes, and snippets.

@justinhernandez
Last active December 21, 2022 14:43
Show Gist options
  • Save justinhernandez/2c390475a0fac3c4b48c1e5d20a52117 to your computer and use it in GitHub Desktop.
Save justinhernandez/2c390475a0fac3c4b48c1e5d20a52117 to your computer and use it in GitHub Desktop.
Rename columns from camelCase to underscore - PostgreSQL
WITH from_table AS (
SELECT 'janky_table'::text AS name
)
SELECT 'ALTER TABLE "' || f.name || '" RENAME COLUMN "' || cols.column_name || '" TO "' || cols.regular_pgstyle || '";' AS stmt
FROM (SELECT column_name,
lower(regexp_replace(column_name, E'([A-Z])', E'\_\\1','g')) As regular_pgstyle
FROM information_schema.columns,
from_table f
WHERE table_name = f.name) cols,
from_table f
WHERE cols.column_name != cols.regular_pgstyle;
@danielpodrazka
Copy link

danielpodrazka commented Dec 21, 2022

I extended it for my use case:

No lowercase in the beginning of column: ( TheHouse -> the_house instead of _the_house)

lower(regexp_replace(column_name, E'([a-z])([A-Z])', E'\\1\_\\2','g')) As regular_pgstyle

Better handling of digits at the beginning( 2022TheHouse -> _2022_the_house instead of 2022the_house)

           regexp_replace(
                   lower(regexp_replace(column_name, E'([a-z])([A-Z])', E'\\1\_\\2','g')),
                   E'^([0-9]+)', E'\_\\1\_','g'
                )

Then I put it in anonymous execution block:

DO $$DECLARE r record;
BEGIN
    FOR r IN
WITH from_table AS (

  SELECT 'janky_table'::text AS name

)
SELECT 'ALTER TABLE ' || f.name || ' RENAME COLUMN "' || cols.column_name || '" TO ' || cols.regular_pgstyle  AS stmt
  FROM (SELECT column_name,
               regexp_replace(
                   lower(regexp_replace(column_name, E'([a-z])([A-Z])', E'\\1\_\\2','g')),
                   E'^([0-9]+)', E'\_\\1\_','g'
                )
                   As regular_pgstyle
          FROM information_schema.columns,
               from_table f
         WHERE table_name = f.name) cols,
        from_table f
 WHERE cols.column_name != cols.regular_pgstyle
    LOOP
        EXECUTE r.stmt;
    END LOOP;
END$$;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment