Re: [HACKERS] Idea on how to simplify comparing two sets

On 2/23/17 3:33 PM, Corey Huinker wrote: I've had to do it with temp tables any time the environment is different between control/experiment, which is the case when you're developing a drop-in replacement for an SRF or view or whatever. FWIW I'd recommend temp views, to give the planner the

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 07, 2017 at 01:03:14PM -0500, Tom Lane wrote: > "David G. Johnston" writes: > > Actually ... now that you mention full join, I believe this works: > > select * from (select ...) s1 full join (select ...) s2 > on ((s1.*)=(s2.*)) where s1.* is distinct

Re: [HACKERS] Idea on how to simplify comparing two sets

> > FWIW I've found myself needing the precursor to this this (give me the > full diff) at least a couple times, and it's quite a PITA on anything but a > trivial relation. > > It's also not possible to make this easier via an SRF because you don't > know in advance what the result set looks like.

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 07, 2017 at 10:58:41AM -0500, Tom Lane wrote: > Joel Jacobson writes: > > Currently there is no simple way to check if two sets are equal. > > Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2 > and SELECT set2 EXCEPT SELECT set1 are both empty? Even

Re: [HACKERS] Idea on how to simplify comparing two sets

On 2/7/17 12:03 PM, Tom Lane wrote: That said I'm not sure how much we want to go down this road on our own. It'd be nice to have when its needed but its not something that gets much visibility on these lists to suggest a large pent-up demand. Yeah, if this isn't in the standard and not in

Re: [HACKERS] Idea on how to simplify comparing two sets

On Wed, Feb 8, 2017 at 10:30 AM, Tom Lane wrote: > David Fetter writes: > > On Wed, Feb 08, 2017 at 11:22:56AM -0500, Tom Lane wrote: > >> Yes. I think a new set-operation keyword would inevitably have to > >> be fully reserved --- UNION, INTERSECT, and

Re: [HACKERS] Idea on how to simplify comparing two sets

David Fetter writes: > On Wed, Feb 08, 2017 at 11:22:56AM -0500, Tom Lane wrote: >> Yes. I think a new set-operation keyword would inevitably have to >> be fully reserved --- UNION, INTERSECT, and EXCEPT all are --- which >> means that you'd break every application that has

Re: [HACKERS] Idea on how to simplify comparing two sets

On Wed, Feb 08, 2017 at 11:22:56AM -0500, Tom Lane wrote: > Robert Haas writes: > > On Wed, Feb 8, 2017 at 4:24 AM, Pantelis Theodosiou > > wrote: > >> I'm not advocating it but I don't see how introducing new SQL keywords > >> breaks backwards

Re: [HACKERS] Idea on how to simplify comparing two sets

Robert Haas writes: > On Wed, Feb 8, 2017 at 4:24 AM, Pantelis Theodosiou > wrote: >> I'm not advocating it but I don't see how introducing new SQL keywords >> breaks backwards compatibility. > It does at least a little bit. Yes. I think a new

Re: [HACKERS] Idea on how to simplify comparing two sets

On Wed, Feb 8, 2017 at 4:24 AM, Pantelis Theodosiou wrote: > I'm not advocating it but I don't see how introducing new SQL keywords > breaks backwards compatibility. It does at least a little bit. This starts failing: select 1 new_keyword form blah; (now you have to insert

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 7, 2017 at 3:13 PM, Joel Jacobson wrote: > Hi hackers, > > Currently there is no simple way to check if two sets are equal. > > Looks like no RDBMS in the world has a simple command for it. > > You have to do something like: > > ... > > Introducing new SQL keywords

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 7, 2017 at 3:58 PM, Tom Lane wrote: > Joel Jacobson writes: > > Currently there is no simple way to check if two sets are equal. > > Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2 > and SELECT set2 EXCEPT SELECT set1 are both empty? >

Re: [HACKERS] Idea on how to simplify comparing two sets

1On Tue, Feb 7, 2017 at 9:46 PM, Joel Jacobson wrote: > On Tue, Feb 7, 2017 at 4:58 PM, Tom Lane wrote: >> Joel Jacobson writes: >>> Currently there is no simple way to check if two sets are equal. >> >> Uh ... maybe check whether SELECT

Re: [HACKERS] Idea on how to simplify comparing two sets

Currently there is no simple way to check if two sets are equal. My 0.02€: Assuming that you mean set = relation, and that there is a key (which should be the case for a set otherwise tuples cannot be distinguished, so this is not really a set), and assuming not null other data, then:

Re: [HACKERS] Idea on how to simplify comparing two sets

"David G. Johnston" writes: > On Tue, Feb 7, 2017 at 8:58 AM, Tom Lane wrote: >> Joel Jacobson writes: >>> Currently there is no simple way to check if two sets are equal. >> Uh ... maybe check whether SELECT set1 EXCEPT SELECT

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 7, 2017 at 8:58 AM, Tom Lane wrote: > Joel Jacobson writes: > > Currently there is no simple way to check if two sets are equal. > > Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2 > and SELECT set2 EXCEPT SELECT set1 are both empty? >

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 7, 2017 at 6:28 PM, David Fetter wrote: > This could be shortened further to the following if we ever implement > DISTINCT for window functions, which might involve implementing > DISTINCT via hashing more generally, which means hashable > types...whee! > > SELECT

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 07, 2017 at 09:10:17AM -0800, David Fetter wrote: > On Tue, Feb 07, 2017 at 04:13:40PM +0100, Joel Jacobson wrote: > > Hi hackers, > > > > Currently there is no simple way to check if two sets are equal. > > Assuming that a and b each has at least one NOT NULL column, is this >

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 07, 2017 at 04:13:40PM +0100, Joel Jacobson wrote: > Hi hackers, > > Currently there is no simple way to check if two sets are equal. Assuming that a and b each has at least one NOT NULL column, is this simple enough? Based on nothing much, I'm assuming here that the IS NOT NULL

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 7, 2017 at 4:58 PM, Tom Lane wrote: > Joel Jacobson writes: >> Currently there is no simple way to check if two sets are equal. > > Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2 > and SELECT set2 EXCEPT SELECT set1 are both empty?

Re: [HACKERS] Idea on how to simplify comparing two sets

Joel Jacobson writes: > Currently there is no simple way to check if two sets are equal. Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2 and SELECT set2 EXCEPT SELECT set1 are both empty? regards, tom lane -- Sent via pgsql-hackers mailing

Re: [HACKERS] Idea on how to simplify comparing two sets

But that's already a valid statement, so there is no ambiguity: SELECT TRUE WHERE FALSE IS NOT DISTINCT FROM (SELECT TRUE); bool -- (0 rows) If you want to compare the set (SELECT TRUE WHERE FALSE) with the set (SELECT TRUE) then just add parenthesis: (SELECT TRUE WHERE FALSE) IS NOT

Re: [HACKERS] Idea on how to simplify comparing two sets

What about this ambiguity? SELECT TRUE WHERE FALSE IS NOT DISTINCT FROM (SELECT TRUE) On Tue, Feb 7, 2017 at 4:13 PM, Joel Jacobson wrote: > Hi hackers, > > Currently there is no simple way to check if two sets are equal. > > Looks like no RDBMS in the world has a simple

[HACKERS] Idea on how to simplify comparing two sets

Hi hackers, Currently there is no simple way to check if two sets are equal. Looks like no RDBMS in the world has a simple command for it. You have to do something like: WITH T1 AS (SELECT * FROM Foo WHERE FooID BETWEEN 1 AND 1), T2 AS (SELECT * FROM Bar WHERE BarID BETWEEN 1 AND 1)