Re: Using generate_series to create a unique ID in a query?

Hello

use temporary sequence instead.
postgres=#create temp sequence a;
CREATE SEQUENCE
postgres=# select nextval('a'), b FROM (values(1),(2),(10),(20)) b(b);
nextval | b
---------+----
1 | 1
2 | 2
3 | 10
4 | 20
(4 rows)

Regards
Pavel Stehule

On 13/11/2007, Sarah Dougherty <sdougherty(at)desc(dot)org> wrote:
> Hello,
>
> I am trying to create a view that will contain a generated sequence
> (unique ID), and am running into problems doing so.
>
> For some context, I am trying to create a report that provides a list of
> client charges and payments and a "running balance" after each
> transaction. Because we often have multiple charges and/or payments on
> the same day, we can't use the transaction date to calculate this
> balance. Instead, I want to calculate our running balance by assigning
> a transaction ID to each transaction a d then having the query sum up
> transaction amounts for all transactions with an equal or lower ID.
>
> I can use generate_series to produce a set of IDs, but can't get it to
> join properly to the rest of my query. For example, if I had 10 rows in
> my query, I would get a series of 1 to 10, but would then get 100 rows
> (10x10) in my result. Ultimately the results of this query are going to
> be used as a view, so I'd like to avoid creating a temp table, sequence,
> etc. Does anyone know how to use generate_series in this manner, or know
> of some other way I can go about this? Thanks in advance!
>
> To recap with an example, the query below works fine, but how do I add a
> series to it?
>
> SELECT * FROM (
>
> SELECT
> client_id,
> effective_date AS transaction_date,
> amount AS charge_amount,
> 0 AS payment_amount
> FROM charge
> UNION
> SELECT
> client_id,
> payment_date AS transaction_date,
> 0 as charge_amount,
> amount AS payment_amount
> FROM payment
>
> ) AS tmp
> ORDER BY
> transaction_date,
> charge_amount<>0 /* order charges before payments */
>
> Thanks,
> Sarah Dougherty
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
>