X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 088549FA159 for ; Sat, 10 Jun 2006 15:15:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 12012-02 for ; Sat, 10 Jun 2006 15:15:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sydney.mailnetwork.co.uk (server88-208-192-113.live-servers.net [88.208.192.113]) by postgresql.org (Postfix) with ESMTP id 0A0309F9AD3 for ; Sat, 10 Jun 2006 15:15:07 -0300 (ADT) Received: from andyshellam-gw.adsl.newnet.co.uk ([80.175.198.137]:3560 helo=[192.168.1.5]) by sydney.mailnetwork.co.uk with esmtpa (Exim 4.60) (envelope-from ) id 1Fp7zF-0002MH-DJ for pgsql-admin@postgresql.org; Sat, 10 Jun 2006 19:15:05 +0100 Message-ID: <448B0C26.8070007@andycc.net> Date: Sat, 10 Jun 2006 19:15:02 +0100 From: Andy Shellam Reply-To: andy@andycc.net Organization: AndyShellam.EU User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Number formatting X-Enigmail-Version: 0.94.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/91 X-Sequence-Number: 22053 Hi, Simple question, which is probably something obvious I'm missing. If anyone can tell me better how to do what I'm doing, go for it! This is for a billing system, and I want PostgreSQL to generate unique reference numbers for each invoice that is something more meaningful than 1, 2, 3 etc. So, what I'm trying to do is use a primary key to generate invoice numbers such as "INV0000001", "INV0000002" etc. Obviously a sequence generates the increments, but when I try formatting it, it adds a space at the beginning of the number. Here's the "default value" for the primary key field: (('INV'::text || to_char((nextval('customer_customer_id_seq'::regclass))::integer, '0000000'::text)))::character varying However what I end up with is: "INV 0000012" If I take the "INV" portion out, and simply try and format the sequence generated, I still get the space added: " 0000013" (to_char((nextval('customer_customer_id_seq'::regclass))::integer, '0000000'::text))::character varying The field is defined as a varchar(10), which allows the 3-digit "INV", and 7 numeric digits. I have tried defining it as char(10) but still get the same. This is on PostgreSQL 8.1.4, Windows XP. Thanks Andy