在PostgreSQL表中生成测试数据

发布时间:2019-11-06 发布网站:大佬教程
大佬教程收集整理的这篇文章主要介绍了在PostgreSQL表中生成测试数据大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我想用一个SELECT查询为这个表创建100个测试行数据:
CREATE TABLE DOCUMENT_TEMPLATE(
   ID INTEGER NOT NULL,NAME TEXT,SHORT_DESCRIPTION TEXT,AUTHOR TEXT,DESCRIPTION TEXT,CONTENT TEXT,LAST_UPDATED DATE,CREATED DATE
);

你能举个例子吗?

解决方法

最抢劫的方法是使用像 SQL Data Generator这样的特定工具.

生成随机数据的简单方法是使用random()和generate_series.

INSERT INTO DOCUMENT_TEMPLATE(id,name,short_description,author,description,content,last_updated,created)
SELECT id,'name',md5(random()::text),'name2',NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100),NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100 + 100)
FROM generate_series(1,100) id;

SqlFiddleDemo

您总是可以更自然地编写自定义函数来生成名字/姓氏/数字/城市/ lorem_ipsum等.

这是我快速编写的内联查询:

INSERT INTO DOCUMENT_TEMPLATE(id,created)
WITH base(id,n1,n2,n3,n4,n5,n6,n7) AS
(
  SELECT id,MIN(CASE WHEN rn = 1 THEN nr END),MIN(CASE WHEN rn = 2 THEN nr END),MIN(CASE WHEN rn = 3 THEN nr END),MIN(CASE WHEN rn = 4 THEN nr END),MIN(CASE WHEN rn = 5 THEN nr END),MIN(CASE WHEN rn = 6 THEN nr END),MIN(CASE WHEN rn = 7 THEN nr END) 
  FROM generate_series(1,100) id     -- number of rows,LATERAL( SELECT nr,ROW_NUMBER() OVER (ORDER BY id * random())
             FROM generate_series(1,900) nr
          ) sub(nr,rn)
   GROUP BY id
),dict(lorem_ipsum,names) AS
(
   SELECT 'Lorem ipsum dolor sit amet,consectetur adipiscing elit. Mauris lacus arcu,blandit non semper elementum,fringilla sodales est. Ut porttitor blandit sapien pellentesque pretium. Donec ut diam sed urna venenatis hendrerit. Nulla eros arcu,mattis vitae congue cursus,tincidunt sed turpis. Curabitur non enim diam,eget elementum dolor. Vivamus enim tortor,tempor at vehicula ac,malesuada id est. Praesent at nibh eget metus dapibus dapibus. Donec arcu orci,sagittis eu interdum vitae,facilisis quis nibh.
Mauris luctus molestie velit,at vestibulum magna cursus sit amet. Nulla in accumsan libero. Donec sed sem lectus. Mauris congue sapien et diam euismod vitae scelerisque diam tincidunt. Praesent a justo enim,vitae venenatis dolor. Donec in tortor at magna dapibus suscipit sit amet a libero. Vivamus porttitor rhoncus tellus,at luctus nisl semper bibendum. Fusce eget accumsan orci. Qout','{"James","John","Jimmy","Jessica","Jeffrey","Jonathan","Justin","Jaclyn","Jodie"}'::text[]
)
SELECT b.id,sub.*
FROM base b,LATERAL (
     SELECT names[b.n1 % 9+1],substring(lorem_ipsum::text,b.n2,20),names[b.n3 % 9+1],b.n4,100),b.n5,200),NOW() - '1 day'::INTERVAL * (b.n6 % 365),(NOW() - '1 day'::INTERVAL * (b.n7 % 365)) - '1 year' :: INTERVAL
      FROM dict
) AS sub(name,descriptionm,created);

SqlFiddleDemo

警告:我知道它可以大大改善.请将它作为起点处理.

大佬总结

以上是大佬教程为你收集整理的在PostgreSQL表中生成测试数据全部内容,希望文章能够帮你解决在PostgreSQL表中生成测试数据所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ群:277859234,请注明来意。