Re: How to insert rows distributed evenly between referenced rows?

But is there a way to join the result of the INSERT...RETURNING that I mentioned with a SELECT from rawData?

On 27/01/2014, at 17:13, Erik Darling wrote:

> I think NTILE() will be your friend here.
>
> http://www.postgresql.org/docs/9.3/static/functions-window.html
> On Jan 27, 2014 10:11 AM, "Herouth Maoz" <herouth(at)unicell(dot)co(dot)il> wrote:
> Basically, I want to do something like this.
>
> I have N rows in table rawData.
> I have to create batches from these N rows using tables batches (which has a serial id column and some additional data columns) and batchContents (which references id in batches), where there will be M rows in batchContent for each row in table batches.
>
> Example (N=12, M=5, meaning take 12 rows, and create as many batches as needed with a maximum of 5 batchContent rows per batch)
>
> rawData
> r01
> r02
> r03
> r04
> r05
> r06
> r07
> r08
> r09
> r10
> r11
> r12
>
> Expected result:
>
> batches:
>
> id post
> 5001 5
> 5002 5
> 5003 5
>
> batchContents:
>
> batch_id datum
> 5001 r01
> 5001 r02
> 5001 r03
> 5001 r04
> 5001 r05
> 5002 r06
> 5002 r07
> 5002 r08
> 5002 r09
> 5002 r10
> 5003 r11
> 5003 r12
>
> The order in which the data are distributed between the batches is not important, but I need to have M data in each batch except the last.
>
> My starting point was a statement for insertion into batches. If I know what N and M are, I know how many batches I'll need (B=ceil(N/M)), so I thought of writing
>
> INSERT INTO batches(post)
> SELECT 5 -- All the extra data, like the "post" field, is inserted as literals here
> FROM generate_series(1,B)
> RETURNING id
>
> This will give me the proper rows in batches, but I don't know how to incorporate this into an insert/select from rawData into batchContent, assuming that I don't want to keep the data programatically and do repeated SELECTs with OFFSET and LIMIT for each id returned. Maybe there is some elegant solution with window functions? Can there be any sort of join over a window or something like that?
>
>
> TIA,
> Herouth
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

--
חרות מעוז
יוניסל פתרונות סלולריים מתקדמים
☎ 03-5181717 שלוחה 742