Postgres sequence from table

Question

I have a table.

ID  name        col lev
1   "Rack 1"    9   7
2   "Rack 2"    6   7
3   "Rack 3"    6   7
4   "Rack 4"    7   7

I want to crate a second table from this one that uses 'col' and 'lev' number as to range. Also, 'lev' needs to be in letters.

The result should be a new tble that looks like this.

ID  Name    col lev
1   Rack 1  1   A
2   Rack 1  1   B
3   Rack 1  1   C
4   Rack 1  1   D
5   Rack 1  1   E
6   Rack 1  1   F
7   Rack 1  1   G
8   Rack 1  2   A
9   Rack 1  2   B
10  Rack 1  2   C
11  Rack 1  2   D
12  Rack 1  2   E
13  Rack 1  2   F
14  Rack 1  2   G
15  Rack 1  3   A
16  Rack 1  3   B
17  Rack 1  3   C
18  Rack 1  3   D
19  Rack 1  3   E
20  Rack 1  3   F
21  Rack 1  3   G

Any ideas?

It's unclear what you're asking! Could you please provide more information? — SelVazi, Aug 18 '23 at 14:49
So for Rack 1, you expect to have 63 rows, Rack 2 will have 42 rows, etc? How does the `ID` column fit into this? Does ID continually increment? — Isolated, Aug 18 '23 at 14:54

score 1 · Accepted Answer · answered Aug 18 '23 at 15:18

You can use generate_series to achieve what you're looking for. Also, create another table for converting your lev integers to text values.

create table my_table (
  id integer, 
  name varchar(10), 
  col integer, 
  lev integer
  );
  
insert into my_table values 
(1, 'Rack 1', 9, 7), 
(2, 'Rack 2', 2, 3);

create table lev_to_text (
 lev_val integer, 
 text_val varchar(1)
  );
  
insert into lev_to_text values 
(1, 'A'), 
(2, 'B'), 
(3, 'C'), 
(4, 'D'), 
(5, 'E'), 
(6, 'F'), 
(7, 'G'), 
(8, 'H');

with lev_series as (
  select name, 
    generate_series(1, lev) as lev
  from my_table
)
select m.name, 
  generate_series(1, m.col) as col, 
  ltt.text_val as lev
from my_table m
join lev_series ls
 on m.name = ls.name
join lev_to_text ltt
  on ls.lev = ltt.lev_val
order by 1,2,3;

name	col	lev
Rack 1	1	A
Rack 1	1	B
Rack 1	1	C
Rack 1	1	D
Rack 1	1	E
Rack 1	1	F
Rack 1	1	G
Rack 1	2	A
Rack 1	2	B
...	...	...
Rack 1	9	F
Rack 1	9	G
Rack 2	1	A
Rack 2	1	B
Rack 2	1	C
Rack 2	2	A
Rack 2	2	B
Rack 2	2	C

View on DB Fiddle

If you need an arbitrary ID column, then just add a row_number() to your select statement.

Postgres sequence from table

1 Answers1