Drop multiple columns from table in a single statment

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Drop multiple columns from table in a single statment

Post by Josh » Wed Sep 04, 2013 3:43 pm

Hello,

Is there a way to drop multiple columns from a table or will I have to create a separate drop statement for each?

Code: Select all

dbadmin=> create table test (a int, b int, c int, d int);
CREATE TABLE
I can drop one column:

Code: Select all

dbadmin=> alter table test drop column d;
ALTER TABLE
But not more that one:

Code: Select all

dbadmin=> alter table test drop column b, c;
ERROR 4856:  Syntax error at or near "c" at character 33
LINE 1: alter table test drop column b, c;
                                        ^
dbadmin=> alter table test drop column (b, c);
ERROR 4856:  Syntax error at or near "(" at character 30
LINE 1: alter table test drop column (b, c);
                                     ^
Thank you!
Joshua

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Drop muliple columns from table in a single statment

Post by JimKnicely » Fri Sep 06, 2013 4:04 pm

Hi,

From the best of my knowledge we to issue an ALTER TABLE DROP COLUMN statement for each column we want to drop.

Maybe you can issue a SQL statement that can generate all of the DROP statement for you?

Example:

Code: Select all

dbadmin=> create table jim (c1 int, c2 int, c3 int, c4 int, c5 int);
CREATE TABLE
dbadmin=> select 'alter table jim drop column ' || column_name || ';'
dbadmin->   from columns
dbadmin->  where column_name in ('c2', 'c4', 'c5');
            ?column?
---------------------------------
 alter table jim drop column c2;
 alter table jim drop column c4;
 alter table jim drop column c5;
(3 rows)

dbadmin=> alter table jim drop column c2;
ALTER TABLE
dbadmin=>  alter table jim drop column c4;
ALTER TABLE
dbadmin=>  alter table jim drop column c5;
ALTER TABLE
dbadmin=> \d jim;
                                 List of Fields by Tables
  Schema  | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
----------+-------+--------+------+------+---------+----------+-------------+-------------
 snowfall | jim   | c1     | int  |    8 |         | f        | f           |
 snowfall | jim   | c3     | int  |    8 |         | f        | f           |
(2 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”