Generate an Auto-Incrementing Alphabetical Series Using SQL
Jim_Knicely
- Select Field - Administrator
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