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

Search Discussions

  • Andreas Kretschmer at Dec 22, 2009 at 8:04 am

    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
  • Damian Carey at Dec 22, 2009 at 11:41 am
    Andreas,

    Thank you. It worked like a charm!
    I'm still wrapping my head around the FROM in the UPDATE - but it
    worked perfectly.

    I also just used the primary key to order the rows because it is in
    creation order anyway.

    Many thanks for taking the time to respond.

    Cheers,
    -Damian

    On Tue, Dec 22, 2009 at 7:03 PM, A. Kretschmer
    wrote:
    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

    --
    Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-novice

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 22, '09 at 6:50a
activeDec 22, '09 at 11:41a
posts3
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase