Re: [SQL] SQL stored function inserting and returning data in a row.

[Marc Mamin]

> What about
> $$
> INSERT INTO  ;
> select currval('seq_matchmaking_session_id');
> $$ language sql;
> 
> ?

Hello,

I'm not sure that this would return the correct id in case of concurrent
calls to your function.
I'm using following kind of function to manage reference tables:

HTH,

Marc Mamin


CREATE TABLE xxx
(
  id serial NOT NULL,
  mycolumn character varying,
  CONSTRAINT xxx_pk PRIMARY KEY (id) ,
  CONSTRAINT xxx_uk UNIQUE (mycolumn)
)



CREATE OR REPLACE FUNCTION get_or_insert_id_xxx( input_value varchar)
RETURNS INT AS $$

DECLARE
  id_value int;

BEGIN
  select into id_value id from xxx where mycolumn =  input_value;
  IF FOUND THEN
return id_value;
  ELSE
insert into xxx ( mycolumn ) values (  input_value );
return id from xxx where mycolumn =  input_value;
  END IF;

EXCEPTION WHEN unique_violation THEN
return id from xxx where mycolumn =  input_value;

END;
$$ LANGUAGE plpgsql; 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] SQL question: Highest column value of unique column pairs

[Marc Mamin]

Hello Kevin,

I would use "select distinct on" to first isolate the candidates in (1)
and (2) and then reitere the query on this sub result:
(the query below will retrieve the last score, not the best one...)


something like (not tested):

select distinct on (date,name)
date,name,score
from
   
   (select distinct (on date, LName1)
   date,LName1 as name ,score1 as score
   from table
   order by date desc,LName1
   
   union all
   
   select distinct on (date, LName2) 
  date,LName2 as name,score2 as score
   from table
   order by date desc, LName2
   )foo
   
order by date desc,name


regards,

Marc Mamin



 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kevin Jenkins
Sent: Saturday, January 12, 2008 1:10 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] SQL question: Highest column value of unique column pairs

Hi,

I have the following table which holds the result of 1 on 1 matches:

FName1, LName1, Score1, FName2, LName2, Score2, Date
John,   Doe,85  Bill,   Gates,  20  Jan 1.
John,   Archer, 90  John,   Doe,120 Jan 5
Bob,Barker, 70  Calvin, Klien   8   Jan 8
John,   Doe,60  Bill,   Gates,  25  Jan 3.

So columns 1 and 2 hold the first person. Column 3 holds his score. 
Columns 4 and 5 hold the second person. Column 6 holds his score.

I want to return the most recent score for each person (be they an
opponent or myself).  And the resultant table shouldn't care if they are
person 1 or 2.

So the end result would be

FName,   LName, Score, Date
John,Doe,   120Jan 5.
John,Archer 90 Jan 5.
Bob, Barker 70 Jan 8
Bill,Gates  25 Jan 3
Calvin   Klien  8  Jan 8

Thanks for any help!

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] trigger for TRUNCATE?

[Peter Childs]

On 11/01/2008, Simon Riggs <[EMAIL PROTECTED]> wrote:
>
> On Fri, 2008-01-11 at 08:24 +, Richard Huxton wrote:
>
> > I've always considered TRUNCATE to be DDL rather than DML. I mentally
> > group it with DROP TABLE rather than DELETE>
>
> DDL/DML probably isn't the right split, since its then arguable as to
> which group of commands it belongs in.
>
> I see we have 3 types of commands:
>
> 1. Commands that alter the rows in the table
> e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group



I'm not sure Truncate currently 100% fits into this group but I think it
should, ought to, or even might.


2. Commands that change the shape of a table
> e.g. ALTER TABLE add/drop column, change type, constraints etc



Create table, drop table, foreign keys, unique indexes,  and (currently)
truncate (in that is currently the same as a drop followed by a create) also
fit into this group


3. Commands that change the environment of a table
> e.g. foreign keys, indexes, grants, set fillfactor, ANALYZE, VACUUM,
> CLUSTER etc
>


ie commands that don't effect the shape of the table or the data in the
table only the speed and security or the table so foreign keys don't really
fit in this class nor do unique indexes.

Peter.

[SQL] Unescaping text or binary file

[RDyes]

IN your sql statements you use e to escape data going into a binary, or 
text field.  How do you unescape this same data?
Richmond H. Dyes
Monroe Community Hospital
760-6213

[SQL] table column names - search

[Marcin Krawczyk]

Hi all. I would like to know if there's a way to obtain a list of tables
containing specified column name? Using standard LIKE '%string' syntax would
be great.


Regards,
mk

Re: [SQL] table column names - search

[Peter Eisentraut]

Am Montag, 14. Januar 2008 schrieb Marcin Krawczyk:
> Hi all. I would like to know if there's a way to obtain a list of tables
> containing specified column name? Using standard LIKE '%string' syntax
> would be great.

SELECT table_schema, table_name FROM information_schema.columns WHERE 
column_name LIKE '%name%';

Add DISTINCT and other columns to taste.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] UTF8 encoding and non-text data types

[Medi Montaseri]

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 <[EMAIL PROTECTED]> wrote:

> At 02:22 PM 1/13/2008, [EMAIL PROTECTED] wrote:
> >Date: Sat, 12 Jan 2008 14:21:00 -0800
> >From: "Medi Montaseri" <[EMAIL PROTECTED]>
> >To: pgsql-sql@postgresql.org
> >Subject: UTF8 encoding and non-text data types
> >Message-ID:
> ><[EMAIL PROTECTED]>
> >
> >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
>
>

Re: [SQL] table column names - search

[Marcin Krawczyk]

Thanks a lot.

2008/1/14, Peter Eisentraut <[EMAIL PROTECTED]>:
>
> Am Montag, 14. Januar 2008 schrieb Marcin Krawczyk:
> > Hi all. I would like to know if there's a way to obtain a list of tables
> > containing specified column name? Using standard LIKE '%string' syntax
> > would be great.
>
> SELECT table_schema, table_name FROM information_schema.columns WHERE
> column_name LIKE '%name%';
>
> Add DISTINCT and other columns to taste.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>

Re: [SQL] UTF8 encoding and non-text data types

[Tom Lane]

"Medi Montaseri" <[EMAIL PROTECTED]> writes:
> 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

Uh, there is *no* difference between the ASCII and UTF8 representations
of decimal digits, nor of any other character that would be allowed in
input for a decimal field.  I can't tell what your problem really is,
but you have certainly misunderstood or misexplained it.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] UTF8 encoding and non-text data types

[dmp]

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

Re: [SQL] UTF8 encoding and non-text data types

[dmp]

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



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] UTF8 encoding and non-text data types

[Steve Midgley]

On Jan 13, 2008 8:51 PM, Steve Midgley 
<[EMAIL PROTECTED]> wrote:
At 02:22 PM 1/13/2008, 
[EMAIL PROTECTED] 
wrote:

>Date: Sat, 12 Jan 2008 14:21:00 -0800
>From: "Medi Montaseri" < 
[EMAIL PROTECTED]>

>To: pgsql-sql@postgresql.org
>Subject: UTF8 encoding and non-text data types
>Message-ID:
>< 
  [EMAIL PROTECTED]>

>
>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
At 11:01 AM 1/14/2008, Medi Montaseri wrote:
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


Hi Medi,

I agree that you should convert your values in Perl before handing to 
DBI. I'm not familiar with DBI but presumably if you're sending it UTF8 
values it's attempting to quote them or do something with them, that a 
numeric field in Pg can't handle. Can you trap/monitor the exact sql 
statement that is generated by DBI and sent to Pg? That would help a 
lot in knowing what it is doing, but I suspect if you just convert your 
numbers from the HTML/UTF8 source values into actual Perl numeric 
values and then ship to DBI you'll be better off. And you'll get some 
input validation for free.


I hope this helps,

Steve

Re: [SQL] UTF8 encoding and non-text data types

[Medi Montaseri]

Here is my traces from perl CGI code, I'll include two samples one in ASCII
and one UTF so we know what to expect

Here is actual SQL statement being executed in Perl and DBI. I do not quote
the numerical value, just provided to DBI raw.

insert into t1 (c1, cost) values ('tewt', 1234)
this works find
insert into t1 (c1, cost) values ('شد',
۱۲۳۴)
 DBD::Pg::db do failed: ERROR:  syntax error at or near ";" at character 59,

And the PG log itself is very similar and says
ERROR:  syntax error at or near ";" at character 59

Char 59 by the way is the first accurance of semi-colon as in 䕱 which
is being caught by PG parser.

Medi


On Jan 14, 2008 12:18 PM, Steve Midgley <[EMAIL PROTECTED]> wrote:

>
> On Jan 13, 2008 8:51 PM, Steve Midgley <[EMAIL PROTECTED]> wrote:
>  At 02:22 PM 1/13/2008, [EMAIL PROTECTED] wrote:
> >Date: Sat, 12 Jan 2008 14:21:00 -0800
> >From: "Medi Montaseri" < [EMAIL PROTECTED]>
> >To: pgsql-sql@postgresql.org
> >Subject: UTF8 encoding and non-text data types
> >Message-ID:
>  >< [EMAIL PROTECTED]>
> >
> >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
> At 11:01 AM 1/14/2008, Medi Montaseri wrote:
> 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
>
>
> Hi Medi,
>
> I agree that you should convert your values in Perl before handing to DBI.
> I'm not familiar with DBI but presumably if you're sending it UTF8 values
> it's attempting to quote them or do something with them, that a numeric
> field in Pg can't handle. Can you trap/monitor the exact sql statement that
> is generated by DBI and sent to Pg? That would help a lot in knowing what it
> is doing, but I suspect if you just convert your numbers from the HTML/UTF8
> source values into actual Perl numeric values and then ship to DBI you'll be
> better off. And you'll get some input validation for free.
>
> I hope this helps,
>
> Steve
>

Re: [SQL] UTF8 encoding and non-text data types

[Steve Midgley]

At 12:43 PM 1/14/2008, Medi Montaseri wrote:
Here is my traces from perl CGI code, I'll include two samples one in 
ASCII and one UTF so we know what to expect


Here is actual SQL statement being executed in Perl and DBI. I do not 
quote the numerical value, just provided to DBI raw.


insert into t1 (c1, cost) values ('tewt', 1234)
this works find
insert into t1 (c1, cost) values ('شد', 
۱۲۳۴)
 DBD::Pg::db do failed: ERROR:  syntax error at or near ";" at 
character 59,


And the PG log itself is very similar and says
ERROR:  syntax error at or near ";" at character 59

Char 59 by the way is the first accurance of semi-colon as in 䕱 
which is being caught by PG parser.


Medi


On Jan 14, 2008 12:18 PM, Steve Midgley 
<[EMAIL PROTECTED]> wrote:


On Jan 13, 2008 8:51 PM, Steve Midgley 
<[EMAIL PROTECTED]> wrote:
At 02:22 PM 1/13/2008, 
[EMAIL PROTECTED] 
wrote:

>Date: Sat, 12 Jan 2008 14:21:00 -0800
>From: "Medi Montaseri" < 
[EMAIL PROTECTED]>

>To: pgsql-sql@postgresql.org
>Subject: UTF8 encoding and non-text data types
>Message-ID:
>< [EMAIL PROTECTED]>

>
>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
At 11:01 AM 1/14/2008, Medi Montaseri wrote:
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


Hi Medi,

I agree that you should convert your values in Perl before handing to 
DBI. I'm not familiar with DBI but presumably if you're sending it 
UTF8 values it's attempting to quote them or do something with them, 
that a numeric field in Pg can't handle. Can you trap/monitor the 
exact sql statement that is generated by DBI and sent to Pg? That 
would help a lot in knowing what it is doing, but I suspect if you 
just convert your numbers from the HTML/UTF8 source values into actual 
Perl numeric values and then ship to DBI you'll be better off. And 
you'll get some input validation for free.


I hope this helps,

Steve


Hi Medi,

That structure for numeric values is never going to work, as best as I 
understand Postgres (and other sql pipes). You have to convert those 
UTF chars to straight numeric format. Hopefully that solves your 
problem? I hope it's not too hard for you to get at the code which is 
sending the numbers as UTF?


Steve

Re: [SQL] UTF8 encoding and non-text data types

[Tom Lane]

"Medi Montaseri" <[EMAIL PROTECTED]> writes:
> insert into t1 (c1, cost) values ('tewt', 1234)
> this works find
> insert into t1 (c1, cost) values ('شد',
> ۱۲۳۴)
>  DBD::Pg::db do failed: ERROR:  syntax error at or near ";" at character 59,

Well, you've got two problems there.  The first and biggest is that
&#NNN; is an HTML notation, not a SQL notation; no SQL database is going
to think that that string in its input is a representation of a single
Unicode character.  The other problem is that even if this did happen,
code points 1777 and nearby are not digits; they're something or other
in Arabic, apparently.  So I think you've got a problem in your Unicode
conversions as well as a notational problem.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] UTF8 encoding and non-text data types

[Tom Lane]

Joe <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Well, you've got two problems there.  The first and biggest is that
>> &#NNN; is an HTML notation, not a SQL notation; no SQL database is going
>> to think that that string in its input is a representation of a single
>> Unicode character.  The other problem is that even if this did happen,
>> code points 1777 and nearby are not digits; they're something or other
>> in Arabic, apparently.
>> 
> Precisely. 1777 through 1780 decimal equate to code points U+06F1 
> through U+06F4, which correspond to the Arabic numerals 1 through 4.

Oh?  Interesting.  But even if we wanted to teach Postgres about that,
wouldn't there be a pretty strong risk of getting confused by Arabic's
right-to-left writing direction?  Wouldn't be real helpful if the entry
came out as 4321 when the user wanted 1234.  Definitely seems like
something that had better be left to the application side, where there's
more context about what the string means.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] UTF8 encoding and non-text data types

[Joe]

Tom Lane wrote:

"Medi Montaseri" <[EMAIL PROTECTED]> writes:
  

insert into t1 (c1, cost) values ('tewt', 1234)
this works find
insert into t1 (c1, cost) values ('شد',
۱۲۳۴)
 DBD::Pg::db do failed: ERROR:  syntax error at or near ";" at character 59,



Well, you've got two problems there.  The first and biggest is that
&#NNN; is an HTML notation, not a SQL notation; no SQL database is going
to think that that string in its input is a representation of a single
Unicode character.  The other problem is that even if this did happen,
code points 1777 and nearby are not digits; they're something or other
in Arabic, apparently.
  
Precisely. 1777 through 1780 decimal equate to code points U+06F1 
through U+06F4, which correspond to the Arabic numerals 1 through 4.


Joe

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] UTF8 encoding and non-text data types

[Joe]

Tom Lane wrote:

Oh?  Interesting.  But even if we wanted to teach Postgres about that,
wouldn't there be a pretty strong risk of getting confused by Arabic's
right-to-left writing direction?  Wouldn't be real helpful if the entry
came out as 4321 when the user wanted 1234.  Definitely seems like
something that had better be left to the application side, where there's
more context about what the string means.
  
The Arabic language is written right-to-left, except ... when it comes 
to numbers.


http://www2.ignatius.edu/faculty/turner/arabic/anumbers.htm

I agree that it's application specific.  The HTML/Perl script ought to 
convert to Western numerals.


Joe

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