时间戳

首页 > 解决方案 > PostgreSQL——如何生成带有外键约束的随机数行假数据？

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

问题描述

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

我正在尝试使用 SQL 生成随机的合成数据来测试我的数据库模式。虽然使用 PostgreSQL 在 PostgreSQL 中生成一堆随机值很容易，但生成保留我期望看到的数据的约束和特征的random()随机数据集却并非如此。具体来说，我有以下表格：

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小提琴

我想填充tests_in_suites随机值，这样每个套件都包含一个随机数（3 到 7 之间）的测试，从tests. 我希望选择是随机且统一的，并避免循环和其他重复模式。我尝试了以下方法：

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小提琴

但它总是为每个套件选择相同数量的测试，并且选择的测试是相同的，因为优化器将子查询替换为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小提琴

如何生成随机数据而不成为查询中优化器或无效数据依赖项的牺牲品？

标签： sqlpostgresqlrandomforeign-keysdata-generation

解决方案

我想用随机值填充tests_in_suites，这样每个套件都包含一个随机数（3到7之间）的测试，从测试中统一选择

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

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() 。

推荐阅读