user494461
user494461

Reputation:

In postgres how to drop all columns but some specific ones from a table

Suppose, I have 8 columns A B C D E F G H in table, and I want to drop all columns but C and E from a table.

How do I do this?

Upvotes: 5

Views: 2208

Answers (2)

murison
murison

Reputation: 3983

If you just know the ones you want to keep, and not the ones you want to drop 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

Upvotes: 2

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

As per my understanding, the only way you can achieve this by just don't include in list of drop column, means Just exclude those columns which you dont want to DROP. Then Use the ALTER Table with DROP Column command

You can DROP multiple column like this way in PostgreSQL

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

Upvotes: 2

Related Questions