Generate an Auto-Incrementing Alphabetical Series Using SQL

Jim_Knicely

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!