From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Juan Jose Natera Abreu <jnatera(at)net-uno(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Workaround or user defined type |
Date: | 2001-11-06 20:27:27 |
Message-ID: | 17600.1005078447@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Juan Jose Natera Abreu <jnatera(at)net-uno(dot)net> writes:
> I am working on a trouble ticket system in Perl, usually those systems
> identify a ticket by a string composed of DDMMYYYY-SERIAL, (DD = day,
> MM= month, YYYY= year, SERIAL= a serial number for the date string).
> I am not sure how to generate these identifiers, in case I generate
> them manually i must provide a mechanism to insure the sequence, like
> locking the table for reading/writing, get the last value, insert the
> new one and then release the lock. However i think this could be a big
> performance killer. Any ideas?
How about
regression=# create sequence serial_seq;
CREATE
regression=# select text(date(now())) || '-' || text(nextval('serial_seq'));
?column?
--------------
2001-11-06-1
(1 row)
regression=# select text(date(now())) || '-' || text(nextval('serial_seq'));
?column?
--------------
2001-11-06-2
(1 row)
If you don't like this particular formatting then you could use to_char
to format the date as you wish.
Note that with this solution the serial numbers increase forever; they
don't reset to 1 at midnight. I consider that a preferable behavior
anyway, but if you want the other, you could have a cron job reset the
sequence object once a day (see setval()).
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2001-11-06 20:37:25 | Re: Workaround or user defined type |
Previous Message | Tom Lane | 2001-11-06 19:49:14 | Re: IS NULL |