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