pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrecord datatype comparisons
10+ messages / 4 participants
[nested] [flat]
* record datatype comparisons
@ 2006-10-31 20:38 George Pavlov <gpavlov@mynewplace.com>
0 siblings, 1 reply; 10+ messages in thread
From: George Pavlov @ 2006-10-31 20:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-10-31 21:22 Andrew Sullivan <ajs@crankycanuck.ca>
parent: George Pavlov <gpavlov@mynewplace.com>
0 siblings, 2 replies; 10+ messages in thread
From: Andrew Sullivan @ 2006-10-31 21:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-10-31 21:33 George Pavlov <gpavlov@mynewplace.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 10+ messages in thread
From: George Pavlov @ 2006-10-31 21:33 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-10-31 21:45 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 10+ messages in thread
From: Volkan YAZICI @ 2006-10-31 21:45 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-10-31 21:49 Alvaro Herrera <alvherre@commandprompt.com>
parent: George Pavlov <gpavlov@mynewplace.com>
0 siblings, 1 reply; 10+ messages in thread
From: Alvaro Herrera @ 2006-10-31 21:49 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-10-31 21:58 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 10+ messages in thread
From: Volkan YAZICI @ 2006-10-31 21:58 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-11-01 14:28 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 1 reply; 10+ messages in thread
From: Andrew Sullivan @ 2006-11-01 14:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-11-01 14:32 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 0 replies; 10+ messages in thread
From: Andrew Sullivan @ 2006-11-01 14:32 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttnet.net.tr>; +Cc: George Pavlov <gpavlov@mynewplace.com>; Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-11-01 21:00 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 10+ messages in thread
From: Volkan YAZICI @ 2006-11-01 21:00 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: record datatype comparisons
@ 2006-11-01 21:07 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 0 replies; 10+ messages in thread
From: Andrew Sullivan @ 2006-11-01 21:07 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttnet.net.tr>; +Cc: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-11-01 21:07 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-31 20:38 record datatype comparisons George Pavlov <gpavlov@mynewplace.com>
2006-10-31 21:22 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-10-31 21:33 ` George Pavlov <gpavlov@mynewplace.com>
2006-10-31 21:49 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-10-31 21:58 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-11-01 14:32 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-10-31 21:45 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-11-01 14:28 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-11-01 21:00 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-11-01 21:07 ` Andrew Sullivan <ajs@crankycanuck.ca>
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