How to enter multiple rows for single value in postgres?

October 27, 2023

I have a table

CREATE TABLE test_tbl(id integer, num integer, name text);

where the data is

INSERT INTO test_tbl (id, num, name)
VALUES(1, 2000, 'fdghgf'),(2, 3000, 'fdghg'),(3, 400, 'fhgf'),(4, 2200, 'dhgf');

I want to insert this data into another table tbl_bifuraction

CREATE TABLE tbl_bifuraction(id integer, num integer, name text, is_bifurcated integer);

so that it inserts multiple rows of 1000 and remaining amount(no extra row when remaining amount is 0) for when the amount is greater than 1000.

Inserted data set should be like :

id  num  name   is_bifurcated
1   1000 fdghgf 1
1   1000 fdghgf 1
2   1000 fdghg  1
2   1000 fdghg  1
2   1000 fdghg  1
3   400  fhgf   0
4   1000 dhgf   1
4   1000 dhgf   1
4   200  dhgf   1

The fiddle link is added for bifuracted_query

>Solution :

The key is to perform a lateral join with generate_series(), so that you get the desired number of result rows:

SELECT t.id,
       CASE WHEN (g.c + 1) * 1000 > t.num
            THEN t.num - g.c * 1000
            ELSE 1000
       END AS num,
       t.name,
       CAST(t.num > 1000 AS integer) AS is_bifurcated
FROM test_tbl AS t
   CROSS JOIN LATERAL generate_series(0, (num - 1) / 1000) AS g(c);

 id │ num  │  name  │ is_bifurcated 
════╪══════╪════════╪═══════════════
  1 │ 1000 │ fdghgf │             1
  1 │ 1000 │ fdghgf │             1
  2 │ 1000 │ fdghg  │             1
  2 │ 1000 │ fdghg  │             1
  2 │ 1000 │ fdghg  │             1
  3 │  400 │ fhgf   │             0
  4 │ 1000 │ dhgf   │             1
  4 │ 1000 │ dhgf   │             1
  4 │  200 │ dhgf   │             1
(9 rows)