SELECT * EXCEPT __raw__

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.

https://forum.vertica.com/discussion/239857/leaving-a-column-out-of-select
https://stackoverflow.com/questions/29095281/how-to-select-all-the-columns-of-a-table-except-one-column/29232177

Answers

  • 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');
     OUTPUT 
    --------
          1
    (1 row)
    
    commit;
    
    select compute_flextable_keys_and_build_view('t');
                             compute_flextable_keys_and_build_view                          
    ----------------------------------------------------------------------------------------
     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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file