CODEWITHSUNDEEP
postgresql
user5325728
user5325728

Reputation: 1

Looking for a way to delete multiple columns from multiple tables in Postgres

I have around 300 tables, each of which had 7 columns added during processing. The names of these columns are the same in each table. We no longer have a use for these columns and must remove them. Is there a way to remove these columns from all tables I specify in batch?

Upvotes: 0

Views: 1177

Answers (2)

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

Use information_schema set of views for this task. You are interested in columns view in particular. Sample code might be the following:

-- test DDL
CREATE TABLE table1 (col1 INTEGER,col2 INTEGER);
CREATE TABLE table2 (col1 INTEGER,col2 INTEGER);

-- possible function definition
CREATE OR REPLACE FUNCTION delete_columns(schema_to_process TEXT,column_list TEXT[]) RETURNS VOID AS
$BODY$
DECLARE
  column_record RECORD;
BEGIN
  FOR column_record IN
    SELECT * FROM information_schema.columns AS cinfo
    WHERE cinfo.table_schema = $1
    AND cinfo.column_name = ANY($2)
  LOOP
    EXECUTE 'ALTER TABLE ' || $1 || '.' || column_record.table_name || ' DROP COLUMN IF EXISTS ' || column_record.column_name || ' CASCADE;';
  END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';

-- sample run
SELECT delete_columns('public',ARRAY['col2']);

Upvotes: 0

keithmo
keithmo

Reputation: 4943

You can take the shotgun approach and do something like:

CREATE FUNCTION alter_tables() RETURNS VOID AS $$
DECLARE
    table_record RECORD;
BEGIN
    FOR table_record IN SELECT table_name::TEXT FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = '<SCHEMA_NAME>' LOOP
        EXECUTE 'ALTER TABLE ' || table_record.table_name || ' DROP COLUMN IF EXISTS <COLUMN_NAME> CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT alter_tables();
DROP FUNCTION alter_tables();

Replace <SCHEMA_NAME> and <COLUMN_NAME> as appropriate.

This will (obviously) not work if you need to be more selective about the exact set of tables to alter.

Upvotes: 1

Related Questions