Thread: custom serial number
hi gurus
i have a problem in generating serial number with the form yyyymm0000 how can i do that?
Mahmoud Al-Ewiwi
Al-Mahawer
Hebron- Palestine
am Tue, dem 18.11.2008, um 1:09:44 -0800 mailte mahmoud ewiwi folgendes:
> hi gurus
> i have a problem in generating serial number with the form yyyymm0000 how can i do that?
test=# create temporary sequence foo;
CREATE SEQUENCE
test=# select to_char(current_date,
'yyyymm')||trim(to_char(nextval('foo'),'09999')); ?column?
-------------20081100001
(1 row)
test=# select to_char(current_date,
'yyyymm')||trim(to_char(nextval('foo'),'09999')); ?column?
-------------20081100002
(1 row)
test=# select to_char(current_date,
'yyyymm')||trim(to_char(nextval('foo'),'09999')); ?column?
-------------20081100003
(1 row)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hello
what do you wont to do exactly?
you can try -
create sequence s;
postgres=# create sequence s;
CREATE SEQUENCE
postgres=# select to_char(current_date, 'yyyymmdd') ||
trim(to_char(nextval('s'), '0000')); ?column?
--------------200811180002
(1 row)
postgres=# select to_char(current_date, 'yyyymmdd') ||
trim(to_char(nextval('s'), '0000')); ?column?
--------------200811180003
(1 row)
regards
Pavel Stehule
2008/11/18 mahmoud ewiwi <mahmoud_ewiwi@yahoo.com>:
> hi gurus
> i have a problem in generating serial number with the form yyyymm0000 how can i do that?
>
> Mahmoud Al-Ewiwi
>
> Al-Mahawer
> Hebron- Palestine
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
am Tue, dem 18.11.2008, um 10:37:23 +0100 mailte Pavel Stehule folgendes:
> Hello
>
> what do you wont to do exactly?
>
> you can try -
>
> create sequence s;
> postgres=# create sequence s;
> CREATE SEQUENCE
> postgres=# select to_char(current_date, 'yyyymmdd') ||
> trim(to_char(nextval('s'), '0000'));
> ?column?
> --------------
> 200811180002
> (1 row)
What's wrong with your sequence, it starts with 2?
SCNR *g*
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hello
2008/11/18 A. Kretschmer <andreas.kretschmer@schollglas.com>:
> am Tue, dem 18.11.2008, um 10:37:23 +0100 mailte Pavel Stehule folgendes:
>> Hello
>>
>> what do you wont to do exactly?
>>
>> you can try -
>>
>> create sequence s;
>> postgres=# create sequence s;
>> CREATE SEQUENCE
>> postgres=# select to_char(current_date, 'yyyymmdd') ||
>> trim(to_char(nextval('s'), '0000'));
>> ?column?
>> --------------
>> 200811180002
>> (1 row)
>
> What's wrong with your sequence, it starts with 2?
>
missing lines :)
>
> SCNR *g*
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
A. Kretschmer wrote:
> am Tue, dem 18.11.2008, um 1:09:44 -0800 mailte mahmoud ewiwi folgendes:
>> hi gurus
>> i have a problem in generating serial number with the form yyyymm0000 how can i do that?
>
> test=# create temporary sequence foo;
> CREATE SEQUENCE
> test=# select to_char(current_date,
> 'yyyymm')||trim(to_char(nextval('foo'),'09999'));
> ?column?
> -------------
> 20081100001
> (1 row)
>
Thats nice. You should now take care about changing months.
I guess a cron job can do the job restarting the sequence the first day
of the month. (If we are talking about Unix/Linux, off course)
Gerardo
am Tue, dem 18.11.2008, um 20:56:07 -0800 mailte mahmoud ewiwi folgendes: > Thank you hery much, it works fine with me , but how can i restart the sequence at the start of each month, or should ido it programatically? Please answer to the list and not to me. You can write a function for that. First, check if is a new month and create a new sequence or reset the sequence. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Thank you very much, it works fine with me , but how can i restart the sequence at the start of each month, or should i doit programatically? Mahmoud Al-Ewiwi Al-Mahawer Hebron- Palestine --- On Wed, 11/19/08, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > From: A. Kretschmer <andreas.kretschmer@schollglas.com> > Subject: Re: [SQL] custom serial number > To: "mahmoud ewiwi" <mahmoud_ewiwi@yahoo.com>, pgsql-sql@postgresql.org > Date: Wednesday, November 19, 2008, 9:45 AM > am Tue, dem 18.11.2008, um 20:56:07 -0800 mailte mahmoud > ewiwi folgendes: > > Thank you hery much, it works fine with me , but how > can i restart the sequence at the start of each month, or > should i do it programatically? > > Please answer to the list and not to me. > > You can write a function for that. First, check if is a new > month and > create a new sequence or reset the sequence. > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: > -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA > http://wwwkeys.de.pgp.net
am Wed, dem 19.11.2008, um 0:52:07 -0800 mailte mahmoud ewiwi folgendes: > Thank you very much, it works fine with me , but how can i restart the sequence at the start of each month, or should ido it programatically? For instance, check if a record for the actual month are in the table. If not, use setval() to reset the sequence to 1. http://www.postgresql.org/docs/8.3/interactive/functions-sequence.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net