Thread: 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@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Bryan Irvine
Sent: Wednesday, September 10, 2003 9:35 AM
To: pgsql-novice@postgresql.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.Elphick@lfix.co.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
> Put quotes around the values you're inserting, like this:
>
> INSERT INTO phonenumbers VALUES ('425', '888', '0773');
That worked.
Thanks
--Bryan
On Wed, Sep 10, 2003 at 12:31:04 -0700, Bryan Irvine <bryan.irvine@kingcountyjournal.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'.)
--- Adrian Holovaty <postgres@holovaty.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