Re: Storing number '001' ?

Actually you probably don't want to *store* those extra zeros, you
simply want to display them.

So simply make sure that you use lpad when you select these values.
For example if I had a table test like this:

CREATE TABLE test (
        foo     integer
);

INSERT INTO test (foo) VALUES (1);
INSERT INTO test (foo) VALUES (2);
INSERT INTO test (foo) VALUES (3);
INSERT INTO test (foo) VALUES (300);

I could then select from it with a query like:

SELECT lpad(foo::text, 3, '0') AS padded_num FROM test;

and get:

 padded_num
------------
 001
 002
 003
 300
(4 rows)

Neat huh!  Of course, you also might want to put a constraint on that
column because otherwise you could have problems.  For example if you
add another value to the table:

INSERT INTO test (foo) VALUES (3000);

and then select use the lpad query you get:

 padded_num
------------
 001
 002
 003
 300
 300
(5 rows)

Which could be bad.

Another tactic would be to simply store the value as a char(3) or
varchar(3) value and do your error checking when you insert the value.
If you are going to be doing math on these values, however, it is
probably a win to store the value as an integer.

Jason

Charles Hauser <chauser@acpub.duke.edu> writes:

> 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'?
>
> Thanks,
> --
> Chuck
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org