Skip site navigation (1) Skip section navigation (2)

Re: Storing number '001' ?

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Charles Hauser <chauser(at)acpub(dot)duke(dot)edu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Storing number '001' ?
Date: 2001-12-07 08:59:52
Message-ID: 1007715593.25679.13.camel@kant.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, 2001-12-06 at 12:33, Charles Hauser wrote:
> Hi,
> 
> I need to store numbers which contain '0' as the first digit like '001', '01' .
> 
> A little history.  A DNA clone_id is denoted by '894001A01.x1'.  I 
> need to sort  clone_id, and have broken it down into its meaningful 
> components:
> 
> project:	894
> plate:		001
> plate row:	A
> plate column: 	01
> read:		x
> ver:		1
> 
> CREATE TABLE clone_fasta (
> project integer NOT NULL,
> plate integer NOT NULL,
> p_row char(1) NOT NULL,
> p_column integer NOT NULL,
> read char(1) NOT NULL,
> ver integer NOT NULL,
> length integer NOT NULL,
> seq text NOT NULL,
> PRIMARY KEY (project,plate,p_row,p_column,read,ver)
> );
> 
> Unfortunately, storing these numbers as integers converts 001 ->1, 
> which I can't use.
> 
> How does one store a number like '001'?

If you have to deal with numbers such that '001' is different to '01'
then they are not numbers: they are _text_.

Therefore: store them in a 'TEXT' field instead.

Also, if you need to sort them into numeric order nonetheless, you could
left-pad with spaces so that '001' becomes '   001' and '01' became
'    01' and so forth.

Or you could just:
... ORDER BY int4(textfield) ...

You can even:
CREATE INDEX myindex ON myfile( int4(textfield));

to have that sort ordering supported by an index.

Regards,
					Andrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267


In response to

pgsql-novice by date

Next:From: Vincent AE ScottDate: 2001-12-07 11:34:06
Subject: IF...THEN...ELSE on INSERT
Previous:From: daniel holtzmanDate: 2001-12-07 07:15:03
Subject: I'll try again: multiple joins

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group