The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!


It would be so convenient if there were a way to exclude the column __raw__ when querying flex tables to avoid the error Duplicate column \"__raw__\" in create table statement. Is there a way to do that?

I understand that in SQL there is no way to "SELECT * EXCEPT col1, col2, ..." (links below) but since Flex tables are a custom extension and the column raw is a custom thing within that, i wonder if this exception exists.


  • Ariel_CaryAriel_Cary Vertica Employee Employee

    Querying flex table views exclude the __raw__ column. Can you share some SQL with what you are trying to do?

    create flex table t();
    insert into t(a, b) values(123, 'xyz');
    (1 row)
    select compute_flextable_keys_and_build_view('t');
     Please see public.t_keys for updated keys
    The view public.t_view is ready for querying
    (1 row)
    select * from t_view;
      a  |  b  
     123 | xyz
    (1 row)
    create table t2 as select * from t_view;
    \d t2
                                       List of Fields by Tables
     Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key 
     public | t2    | a      | int         |    8 |         | f        | f           | 
     public | t2    | b      | varchar(20) |   20 |         | f        | f           | 
    (2 rows)
    select * from t2;
      a  |  b  
     123 | xyz
    (1 row)
  • Thanks! Building the views and querying the views works just as I was hoping.
    The basic use case is creating Vertica tables from arbitrary RFC-4180 CSV data files, and then querying those tables. We have little control over the input files, columns may be missing from some files, extra columns may be present, and I may not know in advance all the columns that each contains, but I just want to merge them using a few keys that I do know exist.

    Flex tables works for that because it gracefully handles querying columns that may not exist in each table.

    A toy example is:

    CREATE TABLE public.zip_county_profile AS
    SELECT * FROM public.zip_county_fips_2021 a
    LEFT JOIN public.us_census_fips_2018 b
    ON (b.statecodefips || b.countycodefips) = a.county;

    This generated an error as there are two raw columns in the resulting table. Building and querying the views solves that.

Leave a Comment

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