Re: phone number list

I have a db that contains phone numbers

The data that get inserted looks like this

425 555 1212

I have a script that inserts that into the DB, the problem is that
sometimes the suffix might start with a 0 and is getting truncated.

so

425 555 0212

ends up being

425 555 212. How can I fix this?

--Bryan

On 10 Sep 2003, Bryan Irvine wrote:

> I have a db that contains phone numbers
>
> The data that get inserted looks like this
>
> 425 555 1212
>
> I have a script that inserts that into the DB, the problem is that
> sometimes the suffix might start with a 0 and is getting truncated.
>
> so
>
> 425 555 0212
>
> ends up being
>
> 425 555 212. How can I fix this?

You are using a numerical field (or fields) for the number,
when you should be using a string. For one thing, the 123-4567
or 123-456-7890 numbers are not universal. The actual format
of the number depends on the locality.

Gord

Make the fields varchar, rather than numeric.
-Louise

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Bryan Irvine
Sent: Wednesday, September 10, 2003 9:35 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] phone number list

I have a db that contains phone numbers

The data that get inserted looks like this

425 555 1212

I have a script that inserts that into the DB, the problem is that
sometimes the suffix might start with a 0 and is getting truncated.

so

425 555 0212

ends up being

425 555 212. How can I fix this?

--Bryan

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Wed, 2003-09-10 at 16:35, Bryan Irvine wrote:
> I have a db that contains phone numbers
>
> The data that get inserted looks like this
>
> 425 555 1212
>
> I have a script that inserts that into the DB, the problem is that
> sometimes the suffix might start with a 0 and is getting truncated.
>
> so
>
> 425 555 0212
>
> ends up being
>
> 425 555 212. How can I fix this?

It sounds as though you are storing the numbers in numeric fields, in
which case store them in text fields instead.

But you need to give much better information if you seriously want
assistance. That is, you need to show us the table structure and the
commands by which you insert a row in that table.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8

> It sounds as though you are storing the numbers in numeric fields, in
> which case store them in text fields instead.
>
> But you need to give much better information if you seriously want
> assistance. That is, you need to show us the table structure and the
> commands by which you insert a row in that table.

I dropped the db and recreated the fields using varchar as Louise
suggested. Here are the commands I'm using to create the tables:

create table phonenumbers (
areacode varchar(3),
prefix varchar(3),
suffix varchar(4)
);

And here's the insert commands:

insert into phonenumbers values (425, 333, 4297);
insert into phonenumbers values (425, 333, 4031);
insert into phonenumbers values (425, 888, 3677);
insert into phonenumbers values (425, 888, 4324);
insert into phonenumbers values (425, 888, 0773);

And here's the results of select:

dncl=# select * from phonenumbers;
areacode | prefix | suffix
----------+--------+--------
425 | 333 | 4297
425 | 333 | 4031
425 | 888 | 3677
425 | 888 | 4324
425 | 888 | 773

Any ideas? Do I need to change the format of the fields again?

--Bryan

Bryan Irvine wrote:
> insert into phonenumbers values (425, 888, 0773);
>
> dncl=# select * from phonenumbers;
> areacode | prefix | suffix
> ----------+--------+--------
> 425 | 888 | 773
>
> Any ideas? Do I need to change the format of the fields again?

Put quotes around the values you're inserting, like this:

INSERT INTO phonenumbers VALUES ('425', '888', '0773');

HTH,
Adrian

On Wed, Sep 10, 2003 at 12:31:04 -0700,
Bryan Irvine <bryan(dot)irvine(at)kingcountyjournal(dot)com> wrote:
>
> I dropped the db and recreated the fields using varchar as Louise
> suggested. Here are the commands I'm using to create the tables:
>
> create table phonenumbers (
> areacode varchar(3),
> prefix varchar(3),
> suffix varchar(4)
> );

I don't think you want to do it that way. If you are just handling
North American numbers without extensions then the area code is
exactly 3 digits, not up to 3 digits. The same applies for what you
call the prefix and suffix.

If you are handling international numbers than you don't want to limit
the number of digits in each section because different countries do
different things (some don't have area or city codes). You will also
need have a location for the country code. (For NA the country code
is '1'.)

> Put quotes around the values you're inserting, like this:
>
> INSERT INTO phonenumbers VALUES ('425', '888', '0773');

That worked.

Thanks

--Bryan

--- Adrian Holovaty <postgres(at)holovaty(dot)com> wrote:
> Bryan Irvine wrote:
> > insert into phonenumbers values (425, 888, 0773);
> >
> > dncl=# select * from phonenumbers;
> > areacode | prefix | suffix
> > ----------+--------+--------
> > 425 | 888 | 773
> >
> > Any ideas? Do I need to change the format of the
> fields again?
>
> Put quotes around the values you're inserting, like
> this:
>
> INSERT INTO phonenumbers VALUES ('425', '888',
> '0773');

The general rule is, string values should be single
quoted, numbers should not be quoted. What appears to
be happening here is that the input is being parsed as
a number (probably int4), then cast silently to
varchar before being inserted.

Considering the strictness that PostgreSQL generally
uses with datatypes, I am slightly stunned to witness
this behaviour. I would have thought that at least a
notice was warranted, if not an outright error.

But: follow the "rules", and you'll be ok.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com