Skip site navigation (1) Skip section navigation (2)

SQL Syntax for ordering a sequence

From: Damian Carey <jamianb(at)gmail(dot)com>
To: Postgresql <pgsql-novice(at)postgresql(dot)org>
Subject: SQL Syntax for ordering a sequence
Date: 2009-12-22 06:50:24
Message-ID: 2bbc8f530912212250g7d313262y700b0971aaa5423d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello,

Firstly, apologies for the genuine novice's novice question, but all
my trawling can't find an answer. Our skills are Java desktop RIA, and
we use Hibernate to do the majority of our SQL lifting - hence our
limitations in understanding what is probably basic SQL.  Any pointers
as to where to find an answer would be most appreciated.

We have a "card" table (with typically a few thousand rows.)  It has a
currently unused column "cardnum" (an integer) that was originally
supposed to have a sequence updating it, but for some unknown reason
was left null - and now we need to use it.

We can easily put a unique sequential value into each row thusly ...

CREATE SEQUENCE card_num_seq START 1;
UPDATE card SET cardnum=nextval('card_num_seq') WHERE card.cardnum IS NULL;

That is almost OK, but we would really like the sequence to be in
"creation order", and the above update is essentially random.

The card table does have a column "creationdate" (a timestamp), so we
can determine the order that the sequence needs to be.

Can anyone please point me towards the appropriate SQL syntax to add
the sequence to our card table in an appropriate card order?

Any pointers would be much appreciated!

Many thanks for your time,
-Damian

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2009-12-22 08:03:04
Subject: Re: SQL Syntax for ordering a sequence
Previous:From: Mladen GogalaDate: 2009-12-20 06:57:03
Subject: Re: Conditionally executing multiple statements in series as single SQL statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group