Select column name that may or may not exist in table, and get null value rather than error

Question

In vertica, is there a way to select a column that may or may not exist in a table, and get null as the value if the column does not exist?

The use case is stacking data from many tables, and writing a script to do that. Not all columns exist in all table, so I would like to write one script like so, and just get null values where a certain column doesn't exist in a particular table.

SELECT * FROM 
(SELECT field1, field2, ... field100 from table1 ) UNION ALL 
(SELECT field1, field2, ... field100 from table2 ) UNION ALL 
(SELECT field1, field2, ... field100 from table3 ) UNION ALL 
(SELECT field1, field2, ... field100 from table4 ) UNION ALL 
...
(SELECT field1, field2, ... field100 from tablen ) UNION ALL 

In our application, it's hard to know in advance whether every table has every named column, and if not, which columns are missing from each table.

EDIT: Reference to same prior question Select columnValue if the column exists otherwise null

Answer 1

If performance does not matter, try Vertica Flex Tables:

DROP TABLE IF EXISTS allcols;                            
DROP TABLE IF EXISTS allbut1;
DROP TABLE IF EXISTS allbut4;

CREATE FLEX TABLE allcols();
INSERT INTO allcols(col1,col2,col3,col4)
          SELECT  1,  2,  3,  4
UNION ALL SELECT 11, 12, 13, 14
;
      
CREATE FLEX TABLE allbut1();
INSERT INTO allbut1(col2,col3,col4)
          SELECT  22, 23, 24
UNION ALL SELECT  32, 33, 34
;
      
CREATE FLEX TABLE allbut4();
INSERT INTO allbut4(col1,col2,col3)
          SELECT  21, 22, 23
UNION ALL SELECT  31, 32, 33
;
COMMIT;
\pset null (null)
          SELECT col1,col2,col3,col4 FROM allcols
UNION ALL SELECT col1,col2,col3,col4 FROM allbut1
UNION ALL SELECT col1,col2,col3,col4 FROM allbut4
;
-- out   col1  | col2 | col3 |  col4  
-- out --------+------+------+--------
-- out  1      | 2    | 3    | 4
-- out  11     | 12   | 13   | 14
-- out  (null) | 22   | 23   | 24
-- out  (null) | 32   | 33   | 34
-- out  21     | 22   | 23   | (null)
-- out  31     | 32   | 33   | (null)