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!
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
0
Answers
Querying flex table views exclude the
__raw__
column. Can you share some SQL with what you are trying to do?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:
This generated an error as there are two raw columns in the resulting table. Building and querying the views solves that.