Reputation:
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
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
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