Quick Tip: Create “Super Wide” Tables

Posted May 27, 2021 by Jim Knicely, Vertica Field Chief Technologist

Tips and Tricks Orange Button

Starting in Vertica 10.1.0, you can go super wide with up to 9,800 column definitions in a table! Prior to Vertica 10.1.x, tables were limited to 1,600 columns.

Example

Creating a table with 9,800 columns:

verticademos=> \! vsql -Atc "SELECT 'CREATE TABLE test_wide_table (' UNION ALL SELECT c || ' INT' || NVL2(LEAD(c) OVER(ORDER BY rn), ',', '') FROM (SELECT row_number() OVER() rn, 'col' || row_number() OVER() c FROM seed LIMIT 9800) foo UNION ALL SELECT ');'" | vsql

CREATE TABLE

Hint: Try to avoid running a SELECT * FROM … a table with 9,800 columns and a trillion+ rows 😊

Have fun!