sql - PostgreSQL——如何生成具有外键约束的随机行数的假数据？

Question

注意:我尝试搜索解决此问题的其他问题和答案，但找不到真正符合我的问题并提供完整解决方案的问题和答案。

我正在尝试使用 SQL 生成随机的合成数据来测试我的数据库架构。虽然使用 random() 在 PostgreSQL 中生成一堆随机值非常容易，但是生成随机数据集来保留我期望看到的数据的约束和特征不是。具体来说，我有以下表格:

CREATE TABLE suites(
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT
);

INSERT INTO suites(name)
SELECT 'suite' || g FROM generate_series(1,50) g;

CREATE TABLE tests(
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT
);

INSERT INTO tests(name)
SELECT 'test' || g FROM generate_series(1,100) g;

CREATE TABLE tests_in_suites(
suite_id BIGINT,
test_id BIGINT,
PRIMARY KEY (suite_id, test_id)
);

DB Fiddle

我想用随机值填充 tests_in_suites，这样每个套件都包含从 tests 中统一选择的随机数量(3 到 7 之间)的测试。我希望选择是随机且均匀的，并避免循环和其他重复模式。我尝试过以下方法:

SELECT s.id, t.id FROM
(select id from suites) s,
(SELECT id FROM tests ORDER BY random() LIMIT 2 + ceil(random() * 5)) t
ORDER BY s.id, t.id;

DB Fiddle

但它总是为每个套件选择相同数量的测试，并且所选的测试是相同的，因为优化器将 s 的子查询替换为常量。我尝试引入对当前正在考虑的套件的依赖关系，但它提示我尝试使用的值无法访问:

SELECT s.id, t.id FROM
(select id from suites) s,
(SELECT id FROM tests ORDER BY random() LIMIT 2 + ceil(random() * 5 + s.id*0)) t
ORDER BY s.id, t.id;

ERROR:  invalid reference to FROM-clause entry for table "s"
LINE 3: ...s ORDER BY random() LIMIT 2 + ceil(random() * 5 + s.id*0)) t
                                                             ^
HINT:  There is an entry for table "s", but it cannot be referenced from this part of the query.

DB Fiddle

如何生成随机数据而不成为查询中优化器或无效数据依赖关系的受害者？

Answer 1

I want to populate tests_in_suites with random values, such that each suite contains a random number (between 3 and 7) of tests, selected uniformly from tests

这听起来像是横向连接的一个很好的用例......

INSERT INTO tests_in_suites(suite_id,test_id)
SELECT suites.id, t.id
FROM suites
CROSS JOIN LATERAL (SELECT id, suites.id AS lol FROM tests ORDER BY random() LIMIT (3+random()*4)) t;

横向连接会为连接左侧表的每一行重新计算连接表，这正是我们想要的。但是如果连接表子查询看起来根本不是依赖子查询，postgres 会对此进行优化。因此，我在连接表中添加了 suites.id，使其看起来像连接表确实依赖于表套件中的行。

也可能有一种方法可以使用 array_agg() 和 unnest() 来做到这一点。