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

CREATE TABLE test(
counter SERIAL,
foobar CHAR(18)
DEFAULT to_char(CURRENT_DATE, 'DDMMYYYY') ||
trim(to_char(nextval('test_counter_seq'),'0000000000')),
tekst TEXT);

I don't know exactly why the white space is in, but the trim function takes
it out.

Best regards,
Andy.

----- Original Message -----
From: "Michiel Lange" <michiel(at)minas(dot)demon(dot)nl>
To: <pgsql-admin(at)postgresql(dot)org>
Sent: Tuesday, March 15, 2005 5:27 PM
Subject: [ADMIN] How to format a date with a serial number for DEFAULT?

> 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 have tried the following
> template_test=# CREATE TEMP TABLE test (
> template_test(# counter SERIAL,
> template_test(# foobar CHAR(18)
> template_test(# DEFAULT CAST(date_part('year',current_date) AS TEXT)
> template_test(# ||
> CAST(CAST(to_char(date_part('month',current_date),'00') AS INT) AS TEXT)
> template_test(# ||
> CAST(CAST(to_char(date_part('day',current_date),'00') AS INT) AS TEXT)
> template_test(# ||
> CAST(CAST(to_char(nextval('test_counter_seq'),'0000000000') AS INT) AS
> TEXT),
> template_test(# tekst TEXT);
>
> This resulted in something almost good, but I lost the padding zeroes.
> I got "20053151"
>
> Without the many CAST's like this:
> template_test=# CREATE TEMP TABLE test (
> template_test(# counter SERIAL,
> template_test(# foobar CHAR(18)
> template_test(# DEFAULT CAST(date_part('year',current_date) AS TEXT)
> template_test(# || to_char(date_part('month',current_date),'00')
> template_test(# || to_char(date_part('day',current_date),'00')
> template_test(# ||
> to_char(nextval('test_counter_seq'),'0000000000'),
> template_test(# tekst TEXT);
>
> Resulted in something almost right as well, but now to_char adds a space
> before each to_char
> I would get a result like "2005 03 05 0000000001"
>
> What options do I have to get this straight?
>
> Mind that I created TEMP tables to test how I should set my default
> value....
> TIA
> Michiel
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>