From: Medi Montaseri <montaseri@gmail.com>
To: Steve Midgley <public@misuse.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: UTF8 encoding and non-text data types
Date: Mon, 14 Jan 2008 11:01:36 -0800
Message-ID: <8078a1730801141101j4bc0dc4chdffee5eaebdb99da@mail.gmail.com> (raw)
In-Reply-To: <478aea79.03098c0a.5f32.ffffa6e4SMTPIN_ADDED@mx.google.com>
References: <20080113222706.73BE416DA2AB@mx1.hub.org>
<478aea79.03098c0a.5f32.ffffa6e4SMTPIN_ADDED@mx.google.com>
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
On Jan 13, 2008 8:51 PM, Steve Midgley <public@misuse.org> wrote:
> At 02:22 PM 1/13/2008, pgsql-sql-owner@postgresql.org wrote:
> >Date: Sat, 12 Jan 2008 14:21:00 -0800
> >From: "Medi Montaseri" <montaseri@gmail.com>
> >To: pgsql-sql@postgresql.org
> >Subject: UTF8 encoding and non-text data types
> >Message-ID:
> ><8078a1730801121421l1c9b90c1lc65d36cf6e752a6f@mail.gmail.com>
> >
> >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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: montaseri@gmail.com, public@misuse.org
Subject: Re: UTF8 encoding and non-text data types
In-Reply-To: <8078a1730801141101j4bc0dc4chdffee5eaebdb99da@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox