Generate an Auto-Incrementing Alphabetical Series Using SQL: Quick Tip

Jim Knicely authored this tip.

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_alpha_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/latest/HTML/index.htm#Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm

Have fun!