updating integer column

hi all, i have a table with an int8 column.
my PHP script should update a row with the new values in the form.
it works fine if i put a number in the field but gives this error if left
blank:

Warning: pg_exec() query failed: ERROR: Bad int8 external representation "" in
/home/httpd/htdocs/forms/manage/update_profile.php on line 140

how can i get around this..
oh and what is the best datatype to use for a 10 digit phone number?.

any help appreciated.
thanks
- bruce

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Bruce Young [ Sa, 22 Februar 2003 17:07 ] [ ID #31326 ]

Re: updating integer column

Hi,

On Sat, 2003-02-22 at 18:07, Bruce Young wrote:
> how can i get around this..

It requires an integer; AFAIK you can't insert blank value there.
> oh and what is the best datatype to use for a 10 digit phone number?.

I always use char type for phone numbers. I think you don't need int
column type. Do you make calculations with the numbers? ;)

Best regards,
--
Devrim GUNDUZ
www.gunduz.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Devrim GUNDUZ [ Sa, 22 Februar 2003 17:56 ] [ ID #31327 ]

Re: updating integer column

> hi all, i have a table with an int8 column.
> my PHP script should update a row with the new values in the form.
> it works fine if i put a number in the field but gives this error if left
> blank:
>
> Warning: pg_exec() query failed: ERROR: Bad int8 external representation "" in
> /home/httpd/htdocs/forms/manage/update_profile.php on line 140
>
> how can i get around this..

Ummm...don't leave it blank???

> oh and what is the best datatype to use for a 10 digit phone number?.

I'd use a varchar...

Chris

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo [at] postgresql.org

Christopher Kings-Lyn [ So, 23 Februar 2003 12:12 ] [ ID #31328 ]

Re: updating integer column

thanks for the reply all. i think i will change the datatype to varchar and
do checks on it.

- bruce

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Bruce Young [ So, 23 Februar 2003 19:14 ] [ ID #31329 ]

Re: updating integer column

This is a multi-part message in MIME format.

------=_NextPart_000_0008_01C2DB9D.30E1D490
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

You can get aroung this type of problem by identifing the filed type and th=
en add an default value of the field when the record is null
provided by PHP .

Make an function to do this .

As.
1. function(DATA_TYPE,PG_FIELD,DATA,DEFAULT_VALUE)

{
// DATA_TYPE is an array that contains the datatype of the PG_FIELD
//PG_FILED is the array of column name in which you would like to insert th=
e data
//DATA is the array of the data you retrived from your form
//DEFAULT_VALUE this is the array of the default values

NOW loop thru the DATA array and when an null value is found
replace it with the deafult value

TIP :
You may use
1 for loop
2. For each loop
}

If you want an working example revert back .
Regards
Aspire420

------=_NextPart_000_0008_01C2DB9D.30E1D490
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>You can get aroung this type of problem by=

identifing the filed type and then add an default value of the field when t=
he
record is null </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>provided by PHP .</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Make an function to do this .</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>As.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>1.
function(DATA_TYPE,PG_FIELD,DATA,DEFAULT_VALUE)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2> {</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>// DATA_TYPE is an array that contai=
ns the
datatype of the PG_FIELD</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>//PG_FILED is the array of column nam=
e in
which you would like to insert the data</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>//DATA is the array of the data you retriv=
ed from
your form</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>//DEFAULT_VALUE this is the array of the d=
efault
values </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>NOW loop thru the DATA array and whe=
n an null
value is found</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>replace it with the deafult value </FONT><=
/DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>TIP :</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>You may use </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>1 for loop</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>2. For each loop</FONT></DIV>
<DIV>}</DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>If you want an working example revert back=

..</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Regards</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Aspire420</FONT></DIV></BODY></HTML>

------=_NextPart_000_0008_01C2DB9D.30E1D490--

Aspire Something [ So, 23 Februar 2003 20:09 ] [ ID #31330 ]

Re: updating integer column

On Sat, 22 Feb 2003, Bruce Young wrote:

> hi all, i have a table with an int8 column.
> my PHP script should update a row with the new values in the form.
> it works fine if i put a number in the field but gives this error if left
> blank:
>
> Warning: pg_exec() query failed: ERROR: Bad int8 external representation "" in
> /home/httpd/htdocs/forms/manage/update_profile.php on line 140
>
> how can i get around this..
> oh and what is the best datatype to use for a 10 digit phone number?.

Hiya Bruce.

Yep, postgresql is doing it's job, making sure you don't stick the wrong
data into the wrong type. For an int, the legal values are all the
numbers from the negative max to positive max, as well as nulls. note
that column constraints can limit this as well, making it positive only,
or no nulls, or only even numbers, etc...

So, if you want to put a null in, just do that:

insert into table (id, number) values (1,NULL);

Secondly, for a phone number, ask yourself how you're going to treat it.
Are you going to do a sum() across the numbers? Or maybe multiply them
together?

If yes, then you should store them as some kind of numeric, int, or as a
float.

If, however, the numbers are not going to be used for math but for
identification, then it is likely that a text / varchar type would be a
better choice.

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

http://www.postgresql.org/users-lounge/docs/faq.html

Scott Marlowe [ Mo, 24 Februar 2003 18:18 ] [ ID #31331 ]

Re: updating integer column

> > oh and what is the best datatype to use for a 10 digit phone number?.
-snip-
> Secondly, for a phone number, ask yourself how you're going to treat it.
> Are you going to do a sum() across the numbers? Or maybe multiply them
> together?
>
> If yes, then you should store them as some kind of numeric, int, or as a
> float.
>
> If, however, the numbers are not going to be used for math but for
> identification, then it is likely that a text / varchar type would be a
> better choice.

Don't use int:
create table foo (ph int);
insert into foo values (5105551212);
ERROR: dtoi4: integer out of range

Use char(10).

Better yet, "properly" normalize phone numbers into area-code (char(3)),
prefix (char(3)) and number (char(4)) fields. This way you can error-check=

your phones against the area-code table, determine approximate geographical=

areas/time-zones, flag dangerous numbers (very high cost off-shore versions=

of 900/976 numbers that look like ordinary phone numbers), etc.

If you really want to you can even include a prefix table to do the same
thing at the exchange level using NANPA data.

Even if you don't use a prefix table, updating your table when area-codes=

split will be easier if the ac and prefix are in their own fields.

It may be overkill for your app but for a variety of reasons is a requireme=
nt
for ours.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo [at] postgresql.org)

Steve Crawford [ Mo, 24 Februar 2003 20:27 ] [ ID #31332 ]

Re: updating integer column

On Mon, 24 Feb 2003, Steve Crawford wrote:

> > > oh and what is the best datatype to use for a 10 digit phone number?.
> -snip-
> > Secondly, for a phone number, ask yourself how you're going to treat it.
> > Are you going to do a sum() across the numbers? Or maybe multiply them
> > together?
> >
> > If yes, then you should store them as some kind of numeric, int, or as a
> > float.
> >
> > If, however, the numbers are not going to be used for math but for
> > identification, then it is likely that a text / varchar type would be a
> > better choice.
>
> Don't use int:
> create table foo (ph int);
> insert into foo values (5105551212);
> ERROR: dtoi4: integer out of range
>
> Use char(10).

Actually, I'd use text or something, because I store international
and US phone numbers. Some are easily 15 or more characters long.

> Better yet, "properly" normalize phone numbers into area-code (char(3)),
> prefix (char(3)) and number (char(4)) fields. This way you can error-check
> your phones against the area-code table, determine approximate geographical
> areas/time-zones, flag dangerous numbers (very high cost off-shore versions
> of 900/976 numbers that look like ordinary phone numbers), etc.

Yes, but then absolutely nothing but US phone numbers will fit. While
that's a great idea if all you're storing are US numbers, it doesn't fit
all models. I don't think it's possible to come up with a regex that
will qualify all the goofy phone numbers my company's database stores.

> If you really want to you can even include a prefix table to do the same
> thing at the exchange level using NANPA data.

We actually do something similar. On campus we have 4 digit numbers, but
we have four different prefixes depending on range. i.e. prefix 123 is
used for say 0001 through 2999, while prefix 456 is used on 3000 through
4499, then 879 for 4500 through 7999 and so on. So we join them based on
range. Works pretty well, but it's ugly.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Scott Marlowe [ Mo, 24 Februar 2003 20:33 ] [ ID #31333 ]

Re: updating integer column

True comments, all - and we haven't even gotten into the problem of telepho=
ne
extensions. But the original question was about 10 digit numbers so I assum=
ed
vanilla US area+prefix+number.

Cheers,
Steve

On Monday 24 February 2003 11:33 am, scott.marlowe wrote:
> On Mon, 24 Feb 2003, Steve Crawford wrote:
> > > > oh and what is the best datatype to use for a 10 digit phone number=
?.
> >
> > -snip-
> >
> > > Secondly, for a phone number, ask yourself how you're going to treat
> > > it. Are you going to do a sum() across the numbers? Or maybe multiply
> > > them together?
> > >
> > > If yes, then you should store them as some kind of numeric, int, or as
> > > a float.
> > >
> > > If, however, the numbers are not going to be used for math but for
> > > identification, then it is likely that a text / varchar type would be=
a
> > > better choice.
> >
> > Don't use int:
> > create table foo (ph int);
> > insert into foo values (5105551212);
> > ERROR: dtoi4: integer out of range
> >
> > Use char(10).
>
> Actually, I'd use text or something, because I store international
> and US phone numbers. Some are easily 15 or more characters long.
>
> > Better yet, "properly" normalize phone numbers into area-code (char(3)),
> > prefix (char(3)) and number (char(4)) fields. This way you can
> > error-check your phones against the area-code table, determine
> > approximate geographical areas/time-zones, flag dangerous numbers (very
> > high cost off-shore versions of 900/976 numbers that look like ordinary
> > phone numbers), etc.
>
> Yes, but then absolutely nothing but US phone numbers will fit. While
> that's a great idea if all you're storing are US numbers, it doesn't fit
> all models. I don't think it's possible to come up with a regex that
> will qualify all the goofy phone numbers my company's database stores.
>
> > If you really want to you can even include a prefix table to do the same
> > thing at the exchange level using NANPA data.
>
> We actually do something similar. On campus we have 4 digit numbers, but
> we have four different prefixes depending on range. i.e. prefix 123 is
> used for say 0001 through 2999, while prefix 456 is used on 3000 through
> 4499, then 879 for 4500 through 7999 and so on. So we join them based on
> range. Works pretty well, but it's ugly.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Steve Crawford [ Di, 25 Februar 2003 01:23 ] [ ID #31334 ]

Re: updating integer column

What sucks is when you go through all the hoops to do this to make sure
your data will be nice and pretty, and the day after you put it online,
you have to rip it out to accomodate international numbers.

Not that anything like that's ever happened to me. Oh no, definitely not.
:-)

On Mon, 24 Feb 2003, Steve Crawford wrote:

> True comments, all - and we haven't even gotten into the problem of telephone
> extensions. But the original question was about 10 digit numbers so I assumed
> vanilla US area+prefix+number.
>
> Cheers,
> Steve
>
> On Monday 24 February 2003 11:33 am, scott.marlowe wrote:
> > On Mon, 24 Feb 2003, Steve Crawford wrote:
> > > > > oh and what is the best datatype to use for a 10 digit phone number?.
> > >
> > > -snip-
> > >
> > > > Secondly, for a phone number, ask yourself how you're going to treat
> > > > it. Are you going to do a sum() across the numbers? Or maybe multiply
> > > > them together?
> > > >
> > > > If yes, then you should store them as some kind of numeric, int, or as
> > > > a float.
> > > >
> > > > If, however, the numbers are not going to be used for math but for
> > > > identification, then it is likely that a text / varchar type would be a
> > > > better choice.
> > >
> > > Don't use int:
> > > create table foo (ph int);
> > > insert into foo values (5105551212);
> > > ERROR: dtoi4: integer out of range
> > >
> > > Use char(10).
> >
> > Actually, I'd use text or something, because I store international
> > and US phone numbers. Some are easily 15 or more characters long.
> >
> > > Better yet, "properly" normalize phone numbers into area-code (char(3)),
> > > prefix (char(3)) and number (char(4)) fields. This way you can
> > > error-check your phones against the area-code table, determine
> > > approximate geographical areas/time-zones, flag dangerous numbers (very
> > > high cost off-shore versions of 900/976 numbers that look like ordinary
> > > phone numbers), etc.
> >
> > Yes, but then absolutely nothing but US phone numbers will fit. While
> > that's a great idea if all you're storing are US numbers, it doesn't fit
> > all models. I don't think it's possible to come up with a regex that
> > will qualify all the goofy phone numbers my company's database stores.
> >
> > > If you really want to you can even include a prefix table to do the same
> > > thing at the exchange level using NANPA data.
> >
> > We actually do something similar. On campus we have 4 digit numbers, but
> > we have four different prefixes depending on range. i.e. prefix 123 is
> > used for say 0001 through 2999, while prefix 456 is used on 3000 through
> > 4499, then 879 for 4500 through 7999 and so on. So we join them based on
> > range. Works pretty well, but it's ugly.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo [at] postgresql.org)

Scott Marlowe [ Di, 25 Februar 2003 01:49 ] [ ID #31335 ]

Datenbanken » gmane.comp.db.postgresql.php » updating integer column

Vorheriges Thema: list archive
Nächstes Thema: tcp connections