sql - Postgresql: Insert the cartesian product of two or more sets -

sql - Postgresql: Insert the cartesian product of two or more sets -

as definition: cartesian product of 2 sets set of possible pairs of these sets, {a,b} x {a,b} = {(a,a),(a,b),(b,a),(b,b)}.

now want insert such cartesian product database table (each pair row). intended fill table default values each pair, data, i.e. 2 sets, not nowadays in database @ point.

any thought how accomplish postgresql?

edit :

with help of grzegorz szpetkowski's reply able produce query want achieve, isn't prettiest one. suppose want insert cartesian product of sets {1,2,3} , {'a','b','c'}.

insert "test" select * (select 1 union select 2 union select 3) p cross bring together (select 'a' union select 'b' union select 'c') q

is there improve way this?

edit2 : accepted reply fine, found version might appropriate if gets more complex:

create temp table "numbers" (id integer) on commit drop; create temp table "chars" (char character varying) on commit drop; insert "numbers" (id) values (1),(2),(3); insert "chars" (char) values ('a'),('b'),('c'); insert "test" select * "numbers" cross bring together "chars";

i not sure if answers question, in postgresql there cross join defined as:

for every possible combination of rows t1 , t2 (i.e., cartesian product), joined table contain row consisting of columns in t1 followed columns in t2. if tables have n , m rows respectively, joined table have n * m rows.

from t1 cross bring together t2 equivalent t1, t2. equivalent t1 inner bring together t2 on true (see below).

edit:

one way utilize values lists (note in fact have no order, utilize order by clause ordering):

select n number, l letter (values (1), (2), (3)) a(n) cross bring together (values ('a'), ('b'), ('c')) b(l);

result:

number | letter --------+-------- 1 | 1 | b 1 | c 2 | 2 | b 2 | c 3 | 3 | b 3 | c (9 rows)

btw:

for more numbers believe it's handle utilize generate_series function, e.g.:

select n number, chr(ascii('a') + l - 1) letter generate_series(1, 5) n cross bring together generate_series(1, 5) l order n, l;

result:

number | letter --------+-------- 1 | 1 | b 1 | c 1 | d 1 | e 2 | 2 | b 2 | c 2 | d 2 | e 3 | 3 | b 3 | c 3 | d 3 | e 4 | 4 | b 4 | c 4 | d 4 | e 5 | 5 | b 5 | c 5 | d 5 | e (25 rows)

sql postgresql cartesian-product