Thread
-
phone number list
Bryan Irvine <bryan.irvine@kingcountyjournal.com> — 2003-09-10T15:35:22Z
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
-
Re: phone number list
ghaverla@freenet.edmonton.ab.ca — 2003-09-10T17:17:07Z
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
-
Re: phone number list
Louise Cofield <lcofield@box-works.com> — 2003-09-10T17:26:41Z
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 -
Re: phone number list
Oliver Elphick <olly@lfix.co.uk> — 2003-09-10T17:34:57Z
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 -
Re: phone number list
Bryan Irvine <bryan.irvine@kingcountyjournal.com> — 2003-09-10T19:31:04Z
> 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
-
Re: phone number list
Adrian Holovaty <postgres@holovaty.com> — 2003-09-10T19:45:27Z
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 -
Re: phone number list
Bruno Wolff III <bruno@wolff.to> — 2003-09-10T20:28:10Z
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'.)
-
Re: phone number list
Bryan Irvine <bryan.irvine@kingcountyjournal.com> — 2003-09-10T20:29:41Z
> Put quotes around the values you're inserting, like this: > > INSERT INTO phonenumbers VALUES ('425', '888', '0773'); That worked. Thanks --Bryan -
Re: phone number list
Jeff Eckermann <jeff_eckermann@yahoo.com> — 2003-09-11T14:10:59Z
--- 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