Re: UTF8 encoding and non-text data types - Mailing list pgsql-sql

Sorry this should have been addressed to Medi
dana.

> Hi Steve,
> Have you tried converting to a decimal type or cast for the cost 
> field? If you
> are gathering this data from a text field and  placing in a variable 
> of type string
> then using that variable in the insert statement it may be rejected 
> because it is not
> type decimal. This  has been my experience with trying to get input 
> data from
> user's textfields and placing in the db.
>
> dana.
>
>> Thanks Steve,
>>
>> Actually I do not insert text data into my numeric field.
>> As I mentioned given
>> create table t1 { name text, cost decimal }
>> then I would like to insert numeric data into column "cost" because 
>> then I can later benefit from numerical operators like SUM, AVG, etc
>>
>> More specifically, I am using HTML, Perl and PG. So from the HTML 
>> point of view a textfield is just some strings. So my user would 
>> enter 12345 but expressed in UTF8. Perl would get this and use DBI to 
>> insert it into PG
>>
>> What I am experiencing now is that DB errors that I am trying to 
>> insert an incorrect data into column "cost" which is numeric and the 
>> data is coming in from HTML in UTF8
>>
>> Mybe I have to convert it to ASCII numbers in Perl before inserting  
>> them into PG
>>
>> Thanks
>> Medi
>>
>> >
>> >I understand PG supports UTF-8 encoding and I have sucessfully
>> >inserted
>> >Unicode text into columns. I was wondering about other data types such
>> >as
>> >numbers, decimal, dates
>> >
>> >That is, say I have a table t1 with
>> >create table t1 { name text, cost decimal }
>> >I can insert UTF8 text datatype into this table with no problem
>> >But if my application attempts to insert numbers encloded in UTF8,
>> >then I
>> >get wrong datatype error
>> >
>> >Is the solution for the application layer (not database) to convert
>> >the
>> >non-text UTF8 numbers to ASCII and then insert it into database ?
>> >
>> >Thanks
>> >Medi
>>
>> Hi Medi,
>>
>> I have only limited experience in this area, but it sounds like you
>> sending your numbers as strings? In your example:
>>
>> >create table t1 { name text, cost decimal };
>>
>> insert into t1 (name, cost) values ('name1', '1');
>>
>> I can't think of how else you're sending numeric values as UTF8? I know
>> that Pg will accept numbers as strings and convert internally (that has
>> worked for me in some object relational environments where I don't
>> choose to cope with data types), but I think it would be better if you
>> simply didn't send your numeric data in quotations, whether as UTF8 or
>> ASCII. If you don't have control over this layer (that quotes your
>> values), then I'd say converting to ASCII would solve the problem. But
>> better to convert to numeric and not ship quoted strings at all.
>>
>> I may be totally off-base and missing something fundamental and I'm
>> very open to correction (by anyone), but that's what I can see here.
>>
>> Best regards,
>>
>> Steve
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org