X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 15C8E563FB for ; Tue, 15 Mar 2005 16:15:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90714-03 for ; Tue, 15 Mar 2005 16:15:02 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id A2A3B52AA2 for ; Tue, 15 Mar 2005 16:14:59 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 8CC9522CB3; Tue, 15 Mar 2005 18:05:42 +0200 (EET) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10716-07; Tue, 15 Mar 2005 18:05:41 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id ABADA22C0C; Tue, 15 Mar 2005 18:05:41 +0200 (EET) Message-ID: <011401c5297a$22fcf800$0b00a8c0@forge> From: "Andrei Bintintan" To: "Michiel Lange" , References: <4236FEF1.7050606@minas.demon.nl> Subject: Re: How to format a date with a serial number for DEFAULT? Date: Tue, 15 Mar 2005 18:14:51 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-15"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.02 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/226 X-Sequence-Number: 16724 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" To: 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 > >