SELECT * EXCEPT __raw__

Pieter_Sheth-Vo

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

Ariel_Cary

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)

Pieter_Sheth-Vo

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.