Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Michiel Lange
Date:
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

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

From
"Andrei Bintintan"
Date:
CREATE TABLE test(
counter SERIAL,
foobar CHAR(100)
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@minas.demon.nl>
To: <pgsql-admin@postgresql.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
>
>

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

From
"Andrei Bintintan"
Date:
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@minas.demon.nl>
To: <pgsql-admin@postgresql.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
>
>

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

From
Robert Perry
Date:
I am far to lazy to bother actually trying it, but I believe prefixing
your format string for the bigint returned by nextval with 'FM' will
eliminate your need for the trim.


On Mar 15, 2005, at 11:15 AM, Andrei Bintintan wrote:

> 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@minas.demon.nl>
> To: <pgsql-admin@postgresql.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
>>
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
>      joining column's datatypes do not match
>

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

From
Edmund Bacon
Date:
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