Re: How to format a date with a serial number for DEFAULT?

michiel@minas.demon.nl (Michiel Lange) writes:

> Hello list,
>
> I am trying to create a table that hould countain a number formatted
> this way: YYYYMMDD##########
>
> Where the hashes should be padded to '0'.

I think you can get to where you want to go with
   to_char(current_date, 'YYYYMMDD')
         || to_char(nextval('test_counter_seq'), 'FM0000000000')


Note that this produces the following results:
 counter |      foobar        |  tekst
---------+--------------------+---------
       1 | 200503150000000002 | able
       3 | 200503150000000004 | baker
       5 | 200503150000000006 | charlie

Eg. foobar and counter are out of step with each other.


using currval instead of nextval produces what you want, perhaps:

 counter |      foobar        |  tekst
---------+--------------------+---------
       1 | 200503150000000001 | able
       2 | 200503150000000002 | baker
       3 | 200503150000000003 | charlie

In this case it cannot  be guarenteed that currval() will return a
valid value, or a distinct value for each row.

 e.g. INSERT INTO test(counter, tekst) VALUES (99, 'Hello World')

This will either insert a row with the same  'counter' portion of
foobar as your most recent insert, or fail because currval() is not
set for you current session.


Maybe you should consider the value of a view:

create table test(
   counter   serial,
   testdate  date DEFALUT current_date,
   data      text);

create view testview as
   select counter, to_char(testdate, 'YYYYMMDD')
                  || to_char(couter, 'FM00000000') as foobar,
           string
   from test2;

This elminates the need to make sure your counter and your
pseudocounter column 'foobar' are in sync.


It occurs to me that maybe what you are trying for is a sequence that
increases from 1 for each new date.  If so I suggest you search the
archives, as this is not so simple.


--
Remove -42 for email