Search Postgresql Archives

Re: Understanding behavior of SELECT with multiple unnested columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 27/03/13 20:36, Ian Lawrence Barwick wrote:

2013/3/27 Ken Tanzer <ken.tanzer@xxxxxxxxx>
Basically you are getting Cartesian joins on the row output of
unnest()

Well that's what I expected too.  Except look at this example, after you delete c:

testdb=# DELETE FROM t2 where val='c';
DELETE 1
testdb=# SELECT * from t1, t2;
 val | val
-----+-----
   1 | a
   1 | b
   2 | a
   2 | b
(4 rows)
 
And compare to:

SELECT unnest(array[1,2]),unnest(array['a','b']);
 unnest | unnest 
--------+--------
      1 | a
      2 | b
(2 rows)

You can see they are not the same! 

Ah yes, what I suggested is actually the equivalent to 
SELECT * FROM unnest(array[1,2]) u1,unnest(array['a','b']) u2;

I seem to recall seeing the explanation for this before, although I'll be
darned if I can remember what it is.

FWIW this happens with other functions returning SETOF:

testdb=# SELECT
testdb-# generate_series(1,2) x,
testdb-# generate_series(1,2) y;
 x | y
---+---
 1 | 1
 2 | 2
(2 rows)

testdb=# SELECT
testdb-# generate_series(1,2) x,
testdb-# generate_series(1,3) y;
 x | y
---+---
 1 | 1
 2 | 2
 1 | 3
 2 | 1
 1 | 2
 2 | 3
(6 rows)

Regards


Ian Barwick



The rule appears to be,
where N_x & N_y are the number of entries returned for x & y:
N_result = is the smallest positive integer that has N_x & N_y as factors.


Cheers,
Gavin

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux