Given the table table1 in PostgreSQL:
number1 | number2 | min_length | max_length 40 | 1801 | 8 | 8 40 | 182 | 8 | 8 42 | 32 | 6 | 8 42 | 4 | 6 | 6 43 | 691 | 9 | 9
I want to create new table table2 like:
start | stop 4018010000 | 4018019999 4018200000 | 4018299999 42320000 | 42329999 423200000 | 423299999 4232000000 | 4232999999 42400000 | 42499999 43691000000 | 43691999999
So the new table will be consisting of:
column_1 = a concatenation of old_column_1 + old_column_2 + a number of "0" equal to (old_column_3 - length of the old_column_2)
column_2 = a concatenation of old_column_1 + old_column_2 + a number of "9" equal to (old_column_3 - length of the old_column_2)
And when min_length is not equal to max_length, I need to take into account all the possible lengths. So for the line "42";"32";6;8 , all the lengths are: 6,7 and 8.
I tried to create the new table2 AS table1, then to create new columns start and stop, then to concatenate the columns 1 and 2 like that:
create table table2 as select * from table1;
alter table table2 add column start text,
add column stop text;
update table2 set start = number1 || number2
For the concatenation of the first 2 columns. But I can't figure out how to do the all concatenation, to add the "0"s and the "9"s.
Assuming all columns are NOT NULL, and max_length is always greater than min_length this does the job:
CREATE TABLE table2 AS
SELECT t.number1::text || rpad(t.number2::text, len, '0') AS start
, t.number1::text || rpad(t.number2::text, len, '9') AS stop
FROM table1 t, generate_series(min_length, max_length) len
db<>fiddle here
The manual for generate_series() and rpad().
If number1 or number2 can be NULL, throw in COALESCE:
SELECT COALESCE(t.number1::text, '') || rpad(COALESCE(t.number2::text,''), len, '0') AS start
, COALESCE(t.number1::text, '') || rpad(COALESCE(t.number2::text,''), len, '9') AS stop
FROM table1 t, generate_series(min_length, max_length) len;
db<>fiddle here
If min_length or max_length can be NULL, you'll have to define what's supposed to happen.