In postgres, how to remove all columns, but some of them from the table

Question

Suppose I have 8 columns ABCDEFGH in a table and I want to drop all columns except C and E from the table.

How to do it?

Answer 1

According to my understanding, the only way to achieve this is to simply not include the drop column in the list, then simply exclude those columns that you do not want DROP. Then use the ALTER Table with DROP Column command

You can DROP multiple columns like this in PostgreSQL

ALTER TABLE table DROP COLUMN A, DROP COLUMN B, DROP COLUMN D, DROP COLUMN F, DROP COLUMN G, DROP COLUMN H;

Answer 2

If you just know the ones you want to keep, and not the ones you want to quit, you can do:

DO $$
DECLARE
    crow record;
    excludes varchar[] := array['C', 'E'];
    yourtab varchar := 'a_table';
BEGIN
    FOR crow IN
        SELECT * FROM information_schema.columns WHERE table_schema = 'public' and table_name = yourtab and column_name != ALL(excludes)
    LOOP
        EXECUTE format ('ALTER TABLE %s DROP COLUMN %s', yourtab, crow.column_name);
    END LOOP;
END;
$$ language plpgsql