SELECT * EXCEPT __raw__

Answers

• Ariel_Cary Vertica Employee Employee

  August 2021

  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)
  

  0
• Pieter_Sheth-Vo ✭✭

  August 2021

  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.

  0