Re: SQL Syntax for ordering a sequence
От | Damian Carey |
---|---|
Тема | Re: SQL Syntax for ordering a sequence |
Дата | |
Msg-id | 2bbc8f530912220341g7783c717p510c67e6fff44dd1@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: SQL Syntax for ordering a sequence ("A. Kretschmer" <andreas.kretschmer@schollglas.com>) |
Список | pgsql-novice |
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 <andreas.kretschmer@schollglas.com> 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 >
В списке pgsql-novice по дате отправления: