PostgreSQL howto create a group of entries representing a matrix of values?

Still a topological base fields are x, y, z, gradient, tmestamp, user now I am at the problem of populating my base with data. I have several patches that have the same characteristic, but a different position. I solved it with preparing an insert statement and two nested loops varying the x, y (z being fixed when filling up a strate (at least at the moment).

Programmatical approach is slow, very slow so I tried to play it in SQL and I got that far:

insert into topo12 ("x","y","z","gradient","user",开发者_如何学C"refresh")
select generate_series(2,4),generate_series(2,4),1024,12222563,'toto',1234567878;

But that doesn't give (logical I know but...) a matrix, but a vector and so far I am struggling with getting further sub-requests working, so any hint appreciated.

I think what you want is a join, so you can produce a Cartesian product of two series:

> SELECT x, y FROM generate_series(1,2) AS x, generate_series(1,2) AS y;
 x | y 
---+---
 1 | 1
 1 | 2
 2 | 1
 2 | 2
(4 rows)

You're currently selecting from two set-returning functions at once. I don't understand what the semantics of that are supposed to be, but it's not producing a Cartesian product of values like you probably want:

> SELECT generate_series(1,2), generate_series(1,2);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
(2 rows)

> SELECT generate_series(1,2), generate_series(1,3);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
               1 |               3
               2 |               1
               1 |               2
               2 |               3
(6 rows)

insert into topo12 ("x","y","z","gradient","user","refresh")
select
    (random() * 2)::int + 2,
    (random() * 2)::int + 2
    ,1024,12222563,'toto',1234567878
from generate_series(1, 10)