pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to format a date with a serial number for DEFAULT?
5+ messages / 4 participants
[nested] [flat]

* How to format a date with a serial number for DEFAULT?
@ 2005-03-15 15:27 Michiel Lange <michiel@minas.demon.nl>
  2005-03-15 16:14 ` Re: How to format a date with a serial number for DEFAULT? Andrei Bintintan <klodoma@ar-sd.net>
  2005-03-15 16:15 ` Re: How to format a date with a serial number for DEFAULT? Andrei Bintintan <klodoma@ar-sd.net>
  2005-03-15 17:14 ` Re: How to format a date with a serial number for DEFAULT? Edmund Bacon <ebacon-xlii@onesystem.com>
  0 siblings, 3 replies; 5+ messages in thread

From: Michiel Lange @ 2005-03-15 15:27 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How to format a date with a serial number for DEFAULT?
  2005-03-15 15:27 How to format a date with a serial number for DEFAULT? Michiel Lange <michiel@minas.demon.nl>
@ 2005-03-15 16:14 ` Andrei Bintintan <klodoma@ar-sd.net>
  2 siblings, 0 replies; 5+ messages in thread

From: Andrei Bintintan @ 2005-03-15 16:14 UTC (permalink / raw)
  To: Michiel Lange <michiel@minas.demon.nl>; pgsql-admin

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
>
> 





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How to format a date with a serial number for DEFAULT?
  2005-03-15 15:27 How to format a date with a serial number for DEFAULT? Michiel Lange <michiel@minas.demon.nl>
@ 2005-03-15 16:15 ` Andrei Bintintan <klodoma@ar-sd.net>
  2005-03-15 17:30   ` Re: How to format a date with a serial number for DEFAULT? Robert Perry <rlperry@lodestonetechnologies.com>
  2 siblings, 1 reply; 5+ messages in thread

From: Andrei Bintintan @ 2005-03-15 16:15 UTC (permalink / raw)
  To: Michiel Lange <michiel@minas.demon.nl>; pgsql-admin

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
>
>





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How to format a date with a serial number for DEFAULT?
  2005-03-15 15:27 How to format a date with a serial number for DEFAULT? Michiel Lange <michiel@minas.demon.nl>
  2005-03-15 16:15 ` Re: How to format a date with a serial number for DEFAULT? Andrei Bintintan <klodoma@ar-sd.net>
@ 2005-03-15 17:30   ` Robert Perry <rlperry@lodestonetechnologies.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Robert Perry @ 2005-03-15 17:30 UTC (permalink / raw)
  To: pgsql-admin

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
>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How to format a date with a serial number for DEFAULT?
  2005-03-15 15:27 How to format a date with a serial number for DEFAULT? Michiel Lange <michiel@minas.demon.nl>
@ 2005-03-15 17:14 ` Edmund Bacon <ebacon-xlii@onesystem.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Edmund Bacon @ 2005-03-15 17:14 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-03-15 17:30 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-15 15:27 How to format a date with a serial number for DEFAULT? Michiel Lange <michiel@minas.demon.nl>
2005-03-15 16:14 ` Andrei Bintintan <klodoma@ar-sd.net>
2005-03-15 16:15 ` Andrei Bintintan <klodoma@ar-sd.net>
2005-03-15 17:30   ` Robert Perry <rlperry@lodestonetechnologies.com>
2005-03-15 17:14 ` Edmund Bacon <ebacon-xlii@onesystem.com>

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