How to drop columns from a table

[usli06]

How do I drop a column from a table? ALTER TABLE DROP COLUMN doesn't seem to work...

[JimKnicely]

The ability to drop a column will be a feature in the next release of Vertica. Until then, there is a work around involving creating a new table without the column, copying the data from the old table to the new table, dropping the old table and then renaming the new table to the old table name.

Example: (I want to drop col2 from the test table)

Code: Select all

dbadmin=> \d test;
                                   List of Fields by Tables
 Schema | Table | Column |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------------+------+---------+----------+-------------+-------------
 public | test  | col1   | varchar(2) |    2 |         | f        | f           |
 public | test  | col2   | varchar(2) |    2 |         | f        | f           |
(2 rows)

dbadmin=> select * from test;
 col1 | col2
------+------
 XX   | YY
(1 row)

dbadmin=> create table test2 (col1 varchar(2));
CREATE TABLE
dbadmin=> insert into test2 select col1 from test;
 OUTPUT
--------
      1
(1 row)

dbadmin=> drop table test;
DROP TABLE
dbadmin=> alter table test2 rename to test;
ALTER TABLE
dbadmin=> \d test
                                   List of Fields by Tables
 Schema | Table | Column |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------------+------+---------+----------+-------------+-------------
 public | test  | col1   | varchar(2) |    2 |         | f        | f           |
(1 row)

dbadmin=> select * from test;
 col1
------
 XX
(1 row)

Don't forget that you'll need to re-grant any privileges on the new table that were on the old one...

[JimKnicely]

I wanted to update this thread to let folks know that we still can't drop a column easily from a table in release 5.1. But remember, were working with data warehouses ...

[jpcavanaugh]

Its important to understand the projections behind the tables in this scenario. If you do it this way, the new table will have base projections and you will lose any custom projections that were defined. I would also comment that you can do a "create table as ..." which will help with some of the projections.

"If the projection was created through the CREATE TABLE AS SELECT statement, the projection uses the sort order, segmentation, and encoding specified for the columns in the query table."

[JimKnicely]

I wonder if it's plausible to generate a script for the table using the EXPORT_OBJECTS function and then use the generated create statements for any non-super projections to recreate them after we've created the new table without the column we want to drop?

For example:

Code: Select all

dbadmin=> create table test (col1 int, col2 varchar, col3 varchar);
CREATE TABLE

dbadmin=> insert into test values (1, 'test1', 'test1');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values (2, 'test2', 'test2');
 OUTPUT
--------
      1
(1 row)

dbadmin=> create projection test_pr as select * from test unsegmented all nodes;
WARNING:  Projection <public.test_pr_node0001> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh.
WARNING:  Projection <public.test_pr_node0002> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh.
WARNING:  Projection <public.test_pr_node0003> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh.
CREATE PROJECTION

dbadmin=> select get_projections('test');
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      get_projections                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Current system K is 1.
# of Nodes: 3.
Table public.test has 6 projections.

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
public.test_pr_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0002, public.test_pr_node0001] [Safe: Yes] [UptoDate: No] [Stats: No]
public.test_pr_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0003, public.test_pr_node0001] [Safe: Yes] [UptoDate: No] [Stats: No]
public.test_pr_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0003, public.test_pr_node0002] [Safe: Yes] [UptoDate: No] [Stats: No]
public.test_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_node0002, public.test_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.test_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_node0003, public.test_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.test_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_node0003, public.test_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]

(1 row)

dbadmin=> select export_objects('/home/dbadmin/test.sql', 'test');
           export_objects
-------------------------------------
 Catalog data exported successfully


dbadmin=> create table test2 as select col1, col2 from test;
CREATE TABLE

dbadmin=> drop table test cascade;
DROP TABLE

dbadmin=> create table test as select * from test2;
CREATE TABLE
dbadmin=> \! cat test.sql


CREATE TABLE public.test
(
    col1 int,
    col2 varchar(80),
    col3 varchar(80)
);



CREATE PROJECTION public.test
(
 col1,
 col2,
 col3
)
AS
 SELECT test.col1,
        test.col2,
        test.col3
 FROM public.test
 ORDER BY test.col1,
          test.col2,
          test.col3
UNSEGMENTED ALL NODES;

CREATE PROJECTION public.test_pr
(
 col1,
 col2,
 col3
)
AS
 SELECT test.col1,
        test.col2,
        test.col3
 FROM public.test
 ORDER BY test.col1,
          test.col2,
          test.col3
UNSEGMENTED ALL NODES;

Modify the non-super projection and run it:

Code: Select all

dbadmin=> CREATE PROJECTION public.test_pr
dbadmin-> (
dbadmin(>  col1,
dbadmin(>  col2
dbadmin(> )
dbadmin-> AS
dbadmin->  SELECT test.col1,
dbadmin->         test.col2
dbadmin->  FROM public.test
dbadmin->  ORDER BY test.col1,
dbadmin->           test.col2
dbadmin-> UNSEGMENTED ALL NODES;
WARNING:  Projection <public.test_pr_node0001> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh.
WARNING:  Projection <public.test_pr_node0002> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh.
WARNING:  Projection <public.test_pr_node0003> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh.
CREATE PROJECTION
dbadmin=> select start_refresh();
             start_refresh
----------------------------------------
 Starting refresh background process.

(1 row)

dbadmin=> drop table test2;
DROP TABLE
dbadmin=> select * from test;
 col1 | col2
------+-------
    1 | test1
    2 | test2
(2 rows)

dbadmin=> select get_projections('test');
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           get_projections                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Current system K is 1.
# of Nodes: 3.
Table public.test has 6 projections.

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
public.test_pr_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0002, public.test_pr_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.test_pr_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0003, public.test_pr_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.test_pr_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0003, public.test_pr_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.test_node0003 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0002, public.test_pr_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.test_node0002 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0003, public.test_pr_node0001] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.test_node0001 [Segmented: No] [Seg Cols: ] [K: 2] [public.test_pr_node0003, public.test_pr_node0002] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]

(1 row)

We're back to the original table and projections minus the col3 column...

But that seems like a lot of work

[JimKnicely]

Great news, Vertica 6.0 supports a DROP COLUMN feature:

Here's an excerpt from the New Feature's Guide:

Using new parameters in the ALTER TABLE statement, you can now perform the following operations:

• Change, reorganize, and remove tble partitions
  Add table columns with a default derived expression
  Drop table columns
  Change a column's data type
  Rename a table or sequence owner

[hinesbrian]

Great news, Vertica 6.0 supports a DROP COLUMN feature:

Here's an excerpt from the New Feature's Guide:

Using new parameters in the ALTER TABLE statement, you can now perform the following operations:

• Change, reorganize, and remove tble partitions
  Add table columns with a default derived expression
  Drop table columns
  Change a column's data type
  Rename a table or sequence owner

i am going to check this out.