Thread: Replacement for Oracle's "ROWNUM" in Postgres

This query is setting a date into a column for all the rows, starting with a
fixed date with 10 minutes intervals.

UPDATE some_table
SET some_date_column = 'some_literal_date' + (ROWNUM / 144.0);

It is used to initialize a table with data.
Is there any way to get the same result in Postgres somehow ?
Of course there is always the possibility to do it in a loop and calculate
the dates in the program, but that sucks.

Thanks,
Csaba.

On Thu, 2002-12-19 at 10:58, Csaba Nagy wrote:
> This query is setting a date into a column for all the rows, starting with a
> fixed date with 10 minutes intervals.
>
> UPDATE some_table
> SET some_date_column = 'some_literal_date' + (ROWNUM / 144.0);
>
> It is used to initialize a table with data.
> Is there any way to get the same result in Postgres somehow ?

One way:

CREATE SEQUENCE dateseq;

UPDATE some_table
SET some_date_column = 'some_literal_date' +
                       (nextval('dateseq') / 144.0)

DROP SEQUENCE dateseq;

b.g.

On 19 Dec 2002 at 11:30, Bill Gribble wrote:

> On Thu, 2002-12-19 at 10:58, Csaba Nagy wrote:
> > This query is setting a date into a column for all the rows, starting with a
> > fixed date with 10 minutes intervals.
> >
> > UPDATE some_table
> > SET some_date_column = 'some_literal_date' + (ROWNUM / 144.0);
> One way:
>
> CREATE SEQUENCE dateseq;
>
> UPDATE some_table
> SET some_date_column = 'some_literal_date' +
>                        (nextval('dateseq') / 144.0)
>
> DROP SEQUENCE dateseq;

Or use OID..

Bye
 Shridhar

--
On-line, adj.:    The idea that a human being should always be accessible to a
computer.

Thanks guys for the answer !
The sequence solution works perfect for me.
Using OIDs is not the best solution, because I expect a sequence running
from 1 to the nr. of updated rows.
BTW, the 1..n sequence should have rang a bell...

Thanks again,
Csaba.


-----Ursprungliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]Im Auftrag von Shridhar
Daithankar
Gesendet: Freitag, 20. Dezember 2002 08:32
An: 'pgsql-general'
Betreff: Re: [GENERAL] Replacement for Oracle's "ROWNUM" in Postgres


On 19 Dec 2002 at 11:30, Bill Gribble wrote:

> On Thu, 2002-12-19 at 10:58, Csaba Nagy wrote:
> > This query is setting a date into a column for all the rows, starting
with a
> > fixed date with 10 minutes intervals.
> >
> > UPDATE some_table
> > SET some_date_column = 'some_literal_date' + (ROWNUM / 144.0);
> One way:
>
> CREATE SEQUENCE dateseq;
>
> UPDATE some_table
> SET some_date_column = 'some_literal_date' +
>                        (nextval('dateseq') / 144.0)
>
> DROP SEQUENCE dateseq;

Or use OID..

Bye
 Shridhar

--
On-line, adj.:    The idea that a human being should always be accessible to a

computer.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html