Lists: | pgsql-general |
---|
From: | Bob Pawley <rjpawley(at)shaw(dot)ca> |
---|---|
To: | Postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Serial Updating |
Date: | 2006-09-03 21:24:41 |
Message-ID: | 005e01c6cf9f$60f66cf0$8e904618@owner |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I have a column that I wish to use to designate loop numbers.
A loop number is composed of a letter (F for flow, T for temperatures etc.) followed by a sequence number starting at one and ending with the last loop.
The loop numbers will appear as F-1, T-2 and so on.
I seem to have no problem designating the column with the appropriate letters.
However, I haven't a clue how to add the numerical portion - or if it is even possible.
If anyone has a thought I would appreciate being pointed to some documentation that covers this.
Bob Pawley
From: | Adrian Klaver <aklaver(at)comcast(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Bob Pawley <rjpawley(at)shaw(dot)ca> |
Subject: | Re: Serial Updating |
Date: | 2006-09-03 22:47:33 |
Message-ID: | 200609031547.33951.aklaver@comcast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Sunday 03 September 2006 02:24 pm, Bob Pawley wrote:
> I have a column that I wish to use to designate loop numbers.
>
> A loop number is composed of a letter (F for flow, T for temperatures etc.)
> followed by a sequence number starting at one and ending with the last
> loop.
>
> The loop numbers will appear as F-1, T-2 and so on.
>
> I seem to have no problem designating the column with the appropriate
> letters.
>
> However, I haven't a clue how to add the numerical portion - or if it is
> even possible.
>
> If anyone has a thought I would appreciate being pointed to some
> documentation that covers this.
>
> Bob Pawley
I am not quite sure of the question here.
To create F-1 you could do 'F'||'-'||1.
If you are asking about creating the serial sequence you may want to look at
the following for an overview of gap less sequences.
http://www.varlena.com/varlena/GeneralBits/130.php
The part on two level sequences would seem to be most appropriate for this
situation.
--
Adrian Klaver
aklaver(at)comcast(dot)net
From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Serial Updating |
Date: | 2006-09-04 05:21:51 |
Message-ID: | 20060904052151.GA10852@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
am Sun, dem 03.09.2006, um 14:24:41 -0700 mailte Bob Pawley folgendes:
> I have a column that I wish to use to designate loop numbers.
>
> A loop number is composed of a letter (F for flow, T for temperatures etc.)
> followed by a sequence number starting at one and ending with the last loop.
>
> The loop numbers will appear as F-1, T-2 and so on.
>
> I seem to have no problem designating the column with the appropriate letters.
>
> However, I haven't a clue how to add the numerical portion - or if it is
> even possible.
I'n not sure if i understand you correctly, but perhaps this example
will be helpful for you:
test=# select * from foo;
col1
------
a
b
a
c
a
(5 rows)
test=*# create sequence s1;
CREATE SEQUENCE
test=*# create sequence s2;
CREATE SEQUENCE
test=*# select col1, case when col1 = 'a' then nextval('s1') when col1 ='b' then nextval('s2') else 0 end from foo;
col1 | case
------+------
a | 1
b | 1
a | 2
c | 0
a | 3
(5 rows)
-- or, with the value from col1:
test=*# rollback;
ROLLBACK
test=# create sequence s1;
CREATE SEQUENCE
test=*# create sequence s2;
CREATE SEQUENCE
test=*# select col1, col1 || '-' || case when col1 = 'a' then nextval('s1') when col1 ='b' then nextval('s2') else 0 end from foo;
col1 | ?column?
------+----------
a | a-1
b | b-1
a | a-2
c | c-0
a | a-3
Read more about conditional expressions:
http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12589
and about sequences:
http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net