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!
Generate an Auto-Incrementing Alphabetical Series Using SQL

One of the very first Vertica questions I was asked from a client was how to generate an auto-incrementing alphabetical series using SQL for use as a primary key in a table.
For example, the user wanted to generate this result set:
AA
AB
AC
AD
AE
…
ZX
ZY
ZZ
We figured out that this was easily done in Vertica using SQL like this:
dbadmin=> SELECT row_number() over (ORDER BY chr(foo.rn) || chr(foo2.rn)) row_number, dbadmin-> chr(foo.rn) || chr(foo2.rn) auto_alpha dbadmin-> FROM (SELECT row_number() over () rn FROM system_tables) foo dbadmin-> CROSS JOIN (SELECT row_number() over () rn FROM system_tables) foo2 dbadmin-> WHERE foo.rn BETWEEN 65 AND 90 dbadmin-> AND foo2.rn BETWEEN 65 AND 90 dbadmin-> ORDER BY chr(foo.rn) || chr(foo2.rn) dbadmin-> LIMIT 5; row_number | auto_alpha ------------+------------ 1 | AA 2 | AB 3 | AC 4 | AD 5 | AE (5 rows) dbadmin=> SELECT * FROM ( dbadmin(> SELECT row_number() over (ORDER BY chr(foo.rn) || chr(foo2.rn)) row_number, dbadmin(> chr(foo.rn) || chr(foo2.rn) auto_alpha dbadmin(> FROM (SELECT row_number() over () rn FROM system_tables) foo dbadmin(> CROSS JOIN (SELECT row_number() over () rn FROM system_tables) foo2 dbadmin(> WHERE foo.rn BETWEEN 65 AND 90 dbadmin(> AND foo2.rn BETWEEN 65 AND 90 dbadmin(> ORDER BY chr(foo.rn) || chr(foo2.rn) dbadmin(> ) foo3 WHERE row_number >= 674; row_number | auto_alpha ------------+------------ 674 | ZX 675 | ZY 676 | ZZ (3 rows)
But setting the primary key of a table to be that auto-incrementing alphabetic series was a bit tricky until Vertica introduced Flattened Tables! Now I can set the default value of a column in a table to be a result of a query.
dbadmin=> CREATE TABLE auto_alpha_increment_values AS dbadmin-> SELECT row_number() over (ORDER BY chr(foo.rn) || chr(foo2.rn)) row_number, dbadmin-> chr(foo.rn) || chr(foo2.rn) auto_alpha dbadmin-> FROM (SELECT row_number() over () rn FROM system_tables) foo dbadmin-> CROSS JOIN (SELECT row_number() over () rn FROM system_tables) foo2 dbadmin-> WHERE foo.rn BETWEEN 65 AND 90 dbadmin-> AND foo2.rn BETWEEN 65 AND 90 dbadmin-> ORDER BY chr(foo.rn) || chr(foo2.rn); CREATE TABLE dbadmin=> CREATE TABLE auto_alpha_increment_test (c INT, c2 VARCHAR(10) DEFAULT (SELECT auto_alpha FROM auto_alpha_increment_values WHERE row_number = auto_apha_increment_test.c) PRIMARY KEY ENABLED); CREATE TABLE dbadmin=> INSERT INTO auto_alpha_increment_test (c) SELECT 1; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO auto_alpha_increment_test (c) SELECT 2; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO auto_alpha_increment_test (c) SELECT 3; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO auto_alpha_increment_test (c) SELECT 4; OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO auto_alpha_increment_test (c) SELECT 5; OUTPUT -------- 1 (1 row) dbadmin=> SELECT * FROM auto_alpha_increment_test ORDER BY c; c | c2 ---+---- 1 | AA 2 | AB 3 | AC 4 | AD 5 | AE (5 rows)
Helpful Link:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm
Have fun!
1