pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Edmund Bacon <ebacon-xlii@onesystem.com>
To: pgsql-admin@postgresql.org
Subject: Re: How to format a date with a serial number for DEFAULT?
Date: 15 Mar 2005 10:14:01 -0700
Message-ID: <m3is3sx25i.fsf@elb_lx.onesystem.ca> (raw)
References: <4236FEF1.7050606@minas.demon.nl>

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



view thread (5+ messages)  latest in thread

Message-ID: <m3is3sx25i.fsf@elb_lx.onesystem.ca>
Permalink:  ../m3is3sx25i.fsf@elb_lx.onesystem.ca/
Also on:    postgresql.org/message-id/m3is3sx25i.fsf@elb_lx.onesystem.ca

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: ebacon-xlii@onesystem.com
  Subject: Re: How to format a date with a serial number for DEFAULT?
  In-Reply-To: <m3is3sx25i.fsf@elb_lx.onesystem.ca>
  References: <4236FEF1.7050606@minas.demon.nl> <m3is3sx25i.fsf@elb_lx.onesystem.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox