Thread: Round Numeric Type

Hi list,<br /><br />I did a trigger to update a points on a table but in some cases the PostgreSQL does a round of my
Numericlike.<br /><br />If >= 0.5 so postgresql puts 1<br />If < 0.5 so postgresql puts 0<br /><br />Did anybody
knowsif it is possible to control this matter ? <br /><br /><br />Regards<br />Ezequias  

I am trying to do some record comparisons using IS DISTINCT FROM and I
feel like I am missing something. Basically comparisons between manually
constructed records work as expected, but if I have a record returned by
a select on one (or both sides) of the comparison I get errors "ERROR:
operator does not exist: record = record". I suspect some simple missing
parentheses/syntax issue but I feel like I have tried everything...

The simplest way to reproduce:

select ((1::int,'a'::varchar) is distinct from (2::int,'a'::varchar));

--> true, as expected

select ((1::int,'a'::varchar) is distinct from (select
(2::int,'a'::varchar)));

--> throws the error

Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of
type record, aren't they?

In real life I want the right side of the IS DISTINCT FROM to be the
result of a query to a table.

Please help. Thanks!

George

On Tue, Oct 31, 2006 at 05:35:17PM -0200, Ezequias Rodrigues da Rocha wrote:
> Hi list,
> 
> I did a trigger to update a points on a table but in some cases the
> PostgreSQL does a round of my Numeric like.
> 
> If >= 0.5 so postgresql puts 1
> If < 0.5 so postgresql puts 0
> 
> Did anybody knows if it is possible to control this matter ?

Sounds like your datatype doesn't match your input, and that you've
got a type that rounds.  What's the datatype you're putting into?

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard 
to use, but at least there is a lot of code underneath."    --Damien Katz

On Tue, Oct 31, 2006 at 12:38:36PM -0800, George Pavlov wrote:
> Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of
> type record, aren't they?

I don't think so.  Psql gives you a hint that not:

testing=# SELECT (1::int, 'a'::varchar); row  
-------(1,a)
(1 row)

testing=# SELECT (SELECT(1::int, 'a'::varchar));?column? 
----------(1,a)
(1 row)

Note the column headers.  They're differently shaped.  Because
pseudotype record doesn't have a shape, equality doesn't make sense,
so you need two shapes that are already identical, so they can use
the matching rules for that.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Everything that happens in the world happens at some place.    --Jane Jacobs 

thanks fo the reply. i was misled by pgAdmin (1.6) giving both as
"record" in the datatype (i thought "row" and "?column?" were just "best
guess" column headers).

so, if they are indeed differently shaped is there any way to make them
be the same shape?

note that this one also fails with the same error (one would think these
are the "same shape"):

select  (select (1,2))
is distinct from  (select (1,2))
;

ERROR: operator does not exist: record = record
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You may
need to add explicit type casts.



> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan
> Sent: Tuesday, October 31, 2006 1:23 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] record datatype comparisons
>
> On Tue, Oct 31, 2006 at 12:38:36PM -0800, George Pavlov wrote:
> > Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of
> > type record, aren't they?
>
> I don't think so.  Psql gives you a hint that not:
>
> testing=# SELECT (1::int, 'a'::varchar);
>   row
> -------
>  (1,a)
> (1 row)
>
> testing=# SELECT (SELECT(1::int, 'a'::varchar));
>  ?column?
> ----------
>  (1,a)
> (1 row)
>
> Note the column headers.  They're differently shaped.  Because
> pseudotype record doesn't have a shape, equality doesn't make sense,
> so you need two shapes that are already identical, so they can use
> the matching rules for that.
>
> A
>
> --
> Andrew Sullivan  | ajs@crankycanuck.ca
> Everything that happens in the world happens at some place.
>         --Jane Jacobs

On Oct 31 04:22, Andrew Sullivan wrote:
> On Tue, Oct 31, 2006 at 12:38:36PM -0800, George Pavlov wrote:
> > Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of
> > type record, aren't they?
> 
> I don't think so.  Psql gives you a hint that not:
> 
> testing=# SELECT (1::int, 'a'::varchar);
>   row  
> -------
>  (1,a)
> (1 row)
> 
> testing=# SELECT (SELECT(1::int, 'a'::varchar));
>  ?column? 
> ----------
>  (1,a)
> (1 row)
> 
> Note the column headers.  They're differently shaped.  Because
> pseudotype record doesn't have a shape, equality doesn't make sense,
> so you need two shapes that are already identical, so they can use
> the matching rules for that.

Can you be more verbose please? I couldn't understand what you mean with
"shape". AFAIK, both above queries should return same TupleDesc, which I
think means they should share same "shape". I don't have an idea about
the row comparison internals, but I still cannot see a reason for the
difference of returned types. I'd be appreciated if you can clarify the
explanation a little bit.


Regards.

George Pavlov wrote:
> thanks fo the reply. i was misled by pgAdmin (1.6) giving both as
> "record" in the datatype (i thought "row" and "?column?" were just "best
> guess" column headers). 
> 
> so, if they are indeed differently shaped is there any way to make them
> be the same shape?
> 
> note that this one also fails with the same error (one would think these
> are the "same shape"):
> 
> select 
>   (select (1,2)) 
> is distinct from 
>   (select (1,2)) 
> ;

This one works:

alvherre=# select row(1,2)
is distinct from row(1,2)
;?column? 
----------f
(1 fila)

Is that what you're after?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Oct 31 06:49, Alvaro Herrera wrote:
> George Pavlov wrote:
> > thanks fo the reply. i was misled by pgAdmin (1.6) giving both as
> > "record" in the datatype (i thought "row" and "?column?" were just "best
> > guess" column headers). 
> > 
> > so, if they are indeed differently shaped is there any way to make them
> > be the same shape?
> > 
> > note that this one also fails with the same error (one would think these
> > are the "same shape"):
> > 
> > select 
> >   (select (1,2)) 
> > is distinct from 
> >   (select (1,2)) 
> > ;
> 
> This one works:
> 
> alvherre=# select
>   row(1,2)
> is distinct from
>   row(1,2)
> ;
>  ?column? 
> ----------
>  f
> (1 fila)

What's the difference between "SELECT (1, 2);" and "SELECT ROW(1, 2);"?


Regards.

Numeric(9,2)




--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

On Tue, Oct 31, 2006 at 11:45:00PM +0200, Volkan YAZICI wrote:
> > 
> > Note the column headers.  They're differently shaped.  Because
> > pseudotype record doesn't have a shape, equality doesn't make sense,
> > so you need two shapes that are already identical, so they can use
> > the matching rules for that.
> 
> Can you be more verbose please? I couldn't understand what you mean with
> "shape". 

It's an analogy, really.  The datatype numeric() for instance, is
determined.  Because it's determined, you can have determinate rules
for comparing one to another.

But the pseudotype record doesn't work that way.  It can accept
whatever you put in there, which is why you can define a variable as
type record, and then select from different tables (or even different
datatypes!) to it in plpgsql: it accommodates these different things. 
The trade-off is that comparing a record to another record won't
always work: because what a record is is not pre-determined, you
can't have determinate rules for comparing one record to another. 
And without determinate rules, you can't have an equality operator.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.     --Dennis Ritchie

On Tue, Oct 31, 2006 at 11:58:08PM +0200, Volkan YAZICI wrote:
> On Oct 31 06:49, Alvaro Herrera wrote:
> > > select 
> > >   (select (1,2)) 
> > > is distinct from 
> > >   (select (1,2)) 
> > > ;

> > alvherre=# select
> >   row(1,2)
> > is distinct from
> >   row(1,2)
> 
> What's the difference between "SELECT (1, 2);" and "SELECT ROW(1, 2);"?

It's not the difference between SELECT (1,2) and SELECT ROW(1,2)
that's relevant, but the difference between SELECT (SELECT (1,2)) and
SELECT ROW(1,2).  

The row's datatype(s) is(are) defined.  The record's datatype isn't. 
So you can have an equality operator for the row.  You can see this
from the error message when you do this:

testing=# SELECT ROW(1,2) is distinct from ROW ('a','b');
ERROR:  invalid input syntax for integer: "a"

This is part of the subtle difference between the record and row
datatypes.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.    --Roger Brinner

On Wed, Nov 01, 2006 at 11:00:04PM +0200, Volkan YAZICI wrote:
> But there should be a way to reach the types of the attributes of the
> record data type. Otherwise, how would it be possible to place it into
> another tuple as an attribute. 

Well, surely there is, but that has to happen at assignment time,
AFAICT.  So you can decide what the record is on the basis of the
datatype, but you can't write an equality operator, because you don't
know what the general rule is going to be in advance.  At least,
that's as far as I'm able to understand how to do this.  But I'm
really past my Latin at this point, because even if I looked at the
code that supports the record type, I wouldn't understand it.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.    --George Orwell

On Nov 01 09:28, Andrew Sullivan wrote:
> On Tue, Oct 31, 2006 at 11:45:00PM +0200, Volkan YAZICI wrote:
> > > Note the column headers.  They're differently shaped.  Because
> > > pseudotype record doesn't have a shape, equality doesn't make sense,
> > > so you need two shapes that are already identical, so they can use
> > > the matching rules for that.
> > 
> > Can you be more verbose please? I couldn't understand what you mean with
> > "shape". 

First, thanks for your answer.

> It's an analogy, really.  The datatype numeric() for instance, is
> determined.  Because it's determined, you can have determinate rules
> for comparing one to another.
> 
> But the pseudotype record doesn't work that way.  It can accept
> whatever you put in there

But there should be a way to reach the types of the attributes of the
record data type. Otherwise, how would it be possible to place it into
another tuple as an attribute. And AFAIK, there're some (hardcoded) ways
to get TupleDesc of a record data type. From this point of view, as long
as you're able to get the TupleDesc of a record data type, you should
have been able to compare it with any other row/record data type.

OTOH, let's assume that we have no idea about attributes of a record
data type. So how does PostgreSQL manage to output it without knowing
attributes' typoutput functions due to their data type OIDs? That sounds
like a contradiction to me.

> which is why you can define a variable as type record, and then select
> from different tables (or even different datatypes!) to it in plpgsql:
> it accommodates these different things. 

IMHO, plpgsql's record data type is a totally different thing.

> The trade-off is that comparing a record to another record won't
> always work: because what a record is is not pre-determined, you
> can't have determinate rules for comparing one record to another. 
> And without determinate rules, you can't have an equality operator.

IIRC, there's some kind of type registration mechanism in the PostgreSQL
internals while forming a record data type. (I'm not sure but, IIRC,
BlessTupleDesc() registers the TupleDesc into somewhere, so it'll be
able to reach its attributes through its registered TupleDesc in the
future.)

Can you point me to related source/README files in the PostgreSQL
source? I read a little bit about it in the backend/parser/*.c and
couldn't see the distinction you explained. Maybe I was looking at wrong
lines.


Regards.