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

Re: SQL Syntax for ordering a sequence

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL Syntax for ordering a sequence
Date: 2009-12-22 08:03:04
Message-ID: 20091222080304.GA10308@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
In response to Damian Carey :
> 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?

Okay, let me try to help you:

test=# select * from damian ;
 id |             ts
----+----------------------------
    | 2009-12-22 08:51:29.629166
    | 2009-12-22 08:46:29.629166
    | 2009-12-22 08:56:29.629166
    | 2009-12-22 09:06:53.325429
    | 2009-12-22 08:26:53.325429
(5 rows)

test=*# create sequence s_damian;
CREATE SEQUENCE
test=*# update damian set id = nextval('s_damian') from (select ts from damian d2 order by ts) foo where foo.ts=damian.ts;
UPDATE 5
test=*# select * from damian order by 2;
 id |             ts
----+----------------------------
  1 | 2009-12-22 08:26:53.325429
  2 | 2009-12-22 08:46:29.629166
  3 | 2009-12-22 08:51:29.629166
  4 | 2009-12-22 08:56:29.629166
  5 | 2009-12-22 09:06:53.325429
(5 rows)


HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

In response to

Responses

pgsql-novice by date

Next:From: Damian CareyDate: 2009-12-22 11:41:10
Subject: Re: SQL Syntax for ordering a sequence
Previous:From: Damian CareyDate: 2009-12-22 06:50:24
Subject: SQL Syntax for ordering a sequence

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