Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

dropping a column from a table

I am trying to drop a column from existing table. But I am not able to drop  a column because super projection is using that column. I tried dropping super projection but no luck. So I ended up dropping table with cascade. 

I do not want to lose history. Drop clears up the history. I could not go back point in time to get data.

At time '2013-01-01...' select --> does not work.

IS there any way around? How do I delete a column with out losing history. I am able to add a column with out losing history.

Comments

  • Did you try to use "RESTRICT" option? Here is an example.
    ex) alter table te drop column col3 restrict;

    For example, if the dropped column is used for segmentation, we cannot drop it. However, if you can create another superprojection which has a differenct segmentation key and drop the old one, you would drop that column.
    You might have already checked, but you can find more detailed information at the following page of the manual.
    https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Tables/DroppingATable...
  • Thanks Kanako.
  • My pleasure:)
  • Greetings everyone,
    I need to run or DROP [COLUMN] on a table, even with the procedure below, it doesn't work:

    https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/DroppingATableColumn.htm#Dropping

    Is there any other solution than to recreate the table?

  • Jim_KnicelyJim_Knicely Administrator

    Can you post an example? Or atleast an error message?

  • edited November 8

    Hello good night,
    Here's an error message, here I'm replacing the table name with "TABLE_TESTE" for information protection.

    => ALTER TABLE "SCHEMA"."TABLE_TESTE" DROP COLUMN "COLUMN_TESTE" CASCADE;
    ROLLBACK 4122: No up-to-date super projection left on the anchor table of projection "TABLE_TESTE_super"
    HINT: Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead
    =>
    
  • SruthiASruthiA Employee

    @joliveira : could you please share the output of select * from system;

  • Follow:

    => select * from system;
     current_epoch | ahm_epoch | last_good_epoch | refresh_epoch | designed_fault_tolerance | node_count | node_down_count | current_fault_tolerance | catalog_revision_number | wos_used_bytes | wos_row_count | ros_used_bytes | ros_row_count | total_used_bytes | total_row_count
    ---------------+-----------+-----------------+---------------+--------------------------+------------+-----------------+-------------------------+-------------------------+----------------+---------------+----------------+---------------+------------------+-----------------
            183663 |    183662 |          183662 |            -1 |                        0 |          1 |               0 |                       0 |                  389286 |              0 |             0 |   257171933517 |   14005825553 |     257171933517 |     14005825553
    (1 row)
    
    =>
    
  • SruthiASruthiA Employee

    @joliveira : Epochs look good. Could you please check if by any chance you are dropping column with the restrictions mentioned in the below documentation

    https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/DroppingATableColumn.htm

    Restrictions
    You cannot drop or alter a primary key column or a column that participates in the table partitioning clause.
    You cannot drop the first column of any projection sort order, or columns that participate in a projection segmentation expression.
    In Enterprise mode, all nodes must be up. This restriction does not apply to Eon mode.
    You cannot drop a column associated with an access policy. Attempts to do so produce the following error:
    ERROR 6482: Failed to parse Access Policies for table "t1"

  • edited November 8

    In this case the column is used in "SEGMENTED BY hash" by the projection, there is no option to "DROP" of column?
    Before, I performed the action recommended in the procedure.

    https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/DroppingATableColumn.htm

    2. If the table is segmented, the target column is specified in the segmentation expression. In this case, the new superprojection must specify a segmentation expression that excludes the target column.

  • SruthiASruthiA Employee

    @joliveira : yes.. there is no way... as mentioned in the hint, the workaround is to create a new projection

  • @SruthiA: Even with the creation of the new projection for the table "SCHEMA"."TABLE_TESTE" without the column "COLUMN_TESTE",
    then executed:
    select refresh('TABLE_TESTE');

    The projection "TABLE_TESTE_super" does not allow the drop of the exemplified column, is there any point that can be done?

    I validated the table projections with: SELECT GET_PROJECTIONS('SCHEMA.TABLE_TESTE');

  • Jim_KnicelyJim_Knicely Administrator

    Should work. Here is a quick example...

    dbadmin=> CREATE TABLE a (c1 INT, c2 INT);
    CREATE TABLE
    
    dbadmin=> INSERT INTO a SELECT 1, 2;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> ALTER TABLE a DROP COLUMN c1 CASCADE;
    ROLLBACK 4122:  No up-to-date super projection left on the anchor table of projection a_super
    HINT:  Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead
    
    dbadmin=> CREATE PROJECTION a_new_super AS SELECT c1, c2 FROM a ORDER BY c2 SEGMENTED BY HASH(c2) ALL NODES;
    WARNING 4468:  Projection <public.a_new_super> 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 refresh('a');
                                                                                                                                               refresh
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Refresh completed with the following outcomes:
    Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
    ----------------------------------------------------------------------------------------
    "public"."a_new_super": [a] [refreshed] [scratch] [0] [0]
    
    (1 row)
    
    dbadmin=> ALTER TABLE a DROP COLUMN c1 CASCADE;
    ALTER TABLE
    
  • edited November 9

    @Jim_Knicely and @SruthiA,

    The procedure was redone, compared to your log, I noticed that the step below did not return any data, what point can I check?

    dbadmin=> SELECT REFRESH('TABLE_TESTE');
                                                                                                                  REFRESH                                           
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Refresh completed with the following outcomes:
    Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
    ----------------------------------------------------------------------------------------
    
    
    (1 row)
    
    dbadmin=>
    
  • Jim_KnicelyJim_Knicely Administrator
    edited November 10

    @joliveira - What do you get when you run this SQL?

    SELECT node_name, projection_name, is_super_projection, is_key_constraint_projection, has_expressions, is_aggregate_projection, aggregate_type, is_segmented, segment_expression, is_up_to_date FROM projections WHERE projection_schema = '<<table_schema>>' AND anchor_table_name = '<<table_name>>';

    Note: Replace 'table_schema' and 'table_name' with your table's schema name and table's name, respectively.

  • @Jim_Knicely,

    Here's spool:

    dbadmin=> SELECT node_name, projection_name, is_super_projection, is_key_constraint_projection, has_expressions, is_aggregate_projection, aggregate_type, is_segmented, segment_expression, is_up_to_date FROM projections WHERE projection_schema = 'SCHEMA' AND anchor_TABLE_TESTE = 'TABLE_TESTE';
     node_name |           projection_name           | is_super_projection | is_key_constraint_projection | has_expressions | is_aggregate_projection | aggregate_type | is_segmented |                                                                segment_expression                                                                | is_up_to_date
    -----------+-------------------------------------+---------------------+------------------------------+-----------------+-------------------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------+---------------
               | TABLE_TESTE_b1                      | f                   | f                            | f               | f                       |                | t            | hash(TABLE_TESTE.C1, TABLE_TESTE.C2, TABLE_TESTE.C3, TABLE_TESTE.C5, TABLE_TESTE.C6, TABLE_TESTE.C7)                                             | t
               | TABLE_TESTE_b0                      | f                   | f                            | f               | f                       |                | t            | hash(TABLE_TESTE.C1, TABLE_TESTE.C2, TABLE_TESTE.C3, TABLE_TESTE.C5, TABLE_TESTE.C6, TABLE_TESTE.C7)                                             | t
               | TABLE_TESTE_old_b1                  | t                   | f                            | f               | f                       |                | t            | hash(TABLE_TESTE.C1, TABLE_TESTE.C2, TABLE_TESTE.C3, TABLE_TESTE.C5, TABLE_TESTE.C6, TABLE_TESTE.C7, TABLE_TESTE.COLUMN_TESTE)                   | t
               | TABLE_TESTE_old_b0                  | t                   | f                            | f               | f                       |                | t            | hash(TABLE_TESTE.C1, TABLE_TESTE.C2, TABLE_TESTE.C3, TABLE_TESTE.C5, TABLE_TESTE.C6, TABLE_TESTE.C7, TABLE_TESTE.COLUMN_TESTE)                   | t
    (4 rows)
    
    
    dbadmin=>
    

    Below is a new test in another homologation environment, in the example below the column I run the "DROP" is "COLUMN_TESTE" which is part of the TABLE_TESTE_old projection (renamed to _old),
    the TABLE_TESTE projection was created without the column "COLUMN_TESTE", then I ran:

    dbadmin=> SELECT REFRESH('TABLE_TESTE');
                                                                                                                                                                                                                          REFRESH
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Refresh completed with the following outcomes:
    Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
    ----------------------------------------------------------------------------------------
    "SCHEMA"."TABLE_TESTE_b1": [TABLE_TESTE] [refreshed] [scratch] [0] [0]
    "SCHEMA"."TABLE_TESTE_b0": [TABLE_TESTE] [refreshed] [scratch] [0] [0]
    
    (1 row)
    
    
    dbadmin=> SELECT MAKE_AHM_NOW();
               MAKE_AHM_NOW
    ----------------------------------
     AHM set (New AHM Epoch: 3544023)
    (1 row)
    
    dbadmin=> ALTER TABLE SCHEMA.TABLE_TESTE DROP COLUMN COLUMN_TESTE CASCADE;
    ROLLBACK 4122:  No up-to-date super projection left on the anchor table of projection TABLE_TESTE_old_b1
    HINT:  Use DROP TABLE ... CASCADE to drop the anchor table and its last projection, or create a replacement super projection instead
    dbadmin=> 
    
  • @Jim_Knicely and @SruthiA,

    Greetings, any news on this subject?
    Grateful for the support.

  • SruthiASruthiA Employee

    @joliveira : Could you please share me the output of the following query

    select export_objects('','SCHEMA.TABLE_TESTE');

  • @SruthiA:

    Firstly grateful for the feedback.
    Follows the requested information.

    CREATE TABLE SCHEMA.TABLE_TESTE
    (
        V1 numeric(40,0) NOT NULL,
        V2 numeric(40,0) NOT NULL,
        V3 numeric(40,0) NOT NULL,
        V4 numeric(40,0) NOT NULL,
        V5 numeric(40,0) NOT NULL,
        V6 numeric(40,0) NOT NULL,
        V7 numeric(40,0) NOT NULL,
        V8 numeric(40,0) NOT NULL,
        D1 numeric(20,0),
        D2 numeric(20,0),
        C1 numeric(20,0) NOT NULL,
        C2 numeric(20,0),
        C3 numeric(20,0),
        COLUMN_TESTE numeric(10,2),
        C4 numeric(20,0),
        C5 numeric(10,0),
        C6 numeric(10,0),
        C7 numeric(10,0) NOT NULL
    );
    
    
    CREATE PROJECTION SCHEMA.TABLE_TESTE_old /*+createtype(L)*/
    (
     V1,
     V2,
     V3,
     V4,
     V5,
     V6,
     V7,
     V8,
     D1,
     D2,
     C1,
     C2,
     C3,
     COLUMN_TESTE,
     C4,
     C5,
     C6,
     C7
    )
    AS
     SELECT TABLE_TESTE.V1,
            TABLE_TESTE.V2,
            TABLE_TESTE.V3,
            TABLE_TESTE.V4,
            TABLE_TESTE.V5,
            TABLE_TESTE.V6,
            TABLE_TESTE.V7,
            TABLE_TESTE.V8,
            TABLE_TESTE.D1,
            TABLE_TESTE.D2,     
            TABLE_TESTE.C1,
            TABLE_TESTE.C2,
            TABLE_TESTE.C3,
            TABLE_TESTE.COLUMN_TESTE,
            TABLE_TESTE.C4,
            TABLE_TESTE.C5,
            TABLE_TESTE.C6,
            TABLE_TESTE.C7
     FROM SCHEMA.TABLE_TESTE
     ORDER BY TABLE_TESTE.V1,
              TABLE_TESTE.V2,
              TABLE_TESTE.V3,
              TABLE_TESTE.V4,
              TABLE_TESTE.V5,
              TABLE_TESTE.V6,
              TABLE_TESTE.V7,
              TABLE_TESTE.V8
    SEGMENTED BY hash(TABLE_TESTE.C1, 
                      TABLE_TESTE.C2, 
                      TABLE_TESTE.C3, 
                      TABLE_TESTE.C4, 
                      TABLE_TESTE.C5, 
                      TABLE_TESTE.C6, 
                      TABLE_TESTE.COLUMN_TESTE, 
                      TABLE_TESTE.C7) ALL NODES KSAFE 1;
    
    CREATE PROJECTION SCHEMA.TABLE_TESTE /*+createtype(L)*/
    (
     V1,
     V2,
     V3,
     V4,
     V5,
     V6,
     V7,
     V8,
     D1,
     D2,
     C1,
     C2,
     C3,
     C4,
     C5,
     C6,
     C7
    )
    AS
     SELECT TABLE_TESTE.V1,
            TABLE_TESTE.V2,
            TABLE_TESTE.V3,
            TABLE_TESTE.V4,
            TABLE_TESTE.V5,
            TABLE_TESTE.V6,
            TABLE_TESTE.V7,
            TABLE_TESTE.V8,
            TABLE_TESTE.D1,
            TABLE_TESTE.D2,     
            TABLE_TESTE.C1,
            TABLE_TESTE.C2,
            TABLE_TESTE.C3,
            TABLE_TESTE.C4,
            TABLE_TESTE.C5,
            TABLE_TESTE.C6,
            TABLE_TESTE.C7
     FROM SCHEMA.TABLE_TESTE
     ORDER BY TABLE_TESTE.V1,
              TABLE_TESTE.V2,
              TABLE_TESTE.V3,
              TABLE_TESTE.V4,
              TABLE_TESTE.V5,
              TABLE_TESTE.V6,
              TABLE_TESTE.V7,
              TABLE_TESTE.V8
    SEGMENTED BY hash(TABLE_TESTE.C1, 
                      TABLE_TESTE.C2, 
                      TABLE_TESTE.C3, 
                      TABLE_TESTE.C4, 
                      TABLE_TESTE.C5, 
                      TABLE_TESTE.C6, 
                      TABLE_TESTE.C7) ALL NODES KSAFE 1;
    
    
    SELECT MARK_DESIGN_KSAFE(1);
    
    

    Note: here I am replacing the table name with "TABLE_TESTE" and also the column names with aliases for information protection.

  • SruthiASruthiA Employee
    edited November 16

    @joliveira : There is an issue with new replacement projection which you created.. vertica will create super projection only if we include all the columns in your projection definition and select list. Please include column teste in the projection definition and try it. I am able to drop the column after refreshing.

    CREATE PROJECTION TABLE_TESTE_super /+createtype(L)/
    (
    V1,
    V2,
    V3,
    V4,
    V5,
    V6,
    V7,
    V8,
    D1,
    D2,
    C1,
    C2,
    C3,
    COLUMN_TESTE,
    C4,
    C5,
    C6,
    C7
    )
    AS
    SELECT TABLE_TESTE.V1,
    TABLE_TESTE.V2,
    TABLE_TESTE.V3,
    TABLE_TESTE.V4,
    TABLE_TESTE.V5,
    TABLE_TESTE.V6,
    TABLE_TESTE.V7,
    TABLE_TESTE.V8,
    TABLE_TESTE.D1,
    TABLE_TESTE.D2,
    TABLE_TESTE.C1,
    TABLE_TESTE.C2,
    TABLE_TESTE.C3,
    TABLE_TESTE.COLUMN_TESTE,
    TABLE_TESTE.C4,
    TABLE_TESTE.C5,
    TABLE_TESTE.C6,
    TABLE_TESTE.C7
    FROM TABLE_TESTE
    ORDER BY TABLE_TESTE.V1,
    TABLE_TESTE.V2,
    TABLE_TESTE.V3,
    TABLE_TESTE.V4,
    TABLE_TESTE.V5,
    TABLE_TESTE.V6,
    TABLE_TESTE.V7,
    TABLE_TESTE.V8
    SEGMENTED BY hash(TABLE_TESTE.C1,
    TABLE_TESTE.C2,
    TABLE_TESTE.C3,
    TABLE_TESTE.C4,
    TABLE_TESTE.C5,
    TABLE_TESTE.C6,
    TABLE_TESTE.C7) ALL NODES KSAFE 1;

    WARNING 4468: Projection <public.TABLE_TESTE_super_super> 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();
    -[ RECORD 1 ]-+---------------------------------------
    start_refresh | Starting refresh background process.

    dbadmin=> select make_ahm_now();
    -[ RECORD 1 ]+----------------------------
    make_ahm_now | AHM set (New AHM Epoch: 65)

    dbadmin=> ALTER TABLE TABLE_TESTE DROP COLUMN COLUMN_TESTE CASCADE;
    ALTER TABLE
    dbadmin=>

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.