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.