GaussDB(DWS)批量生成数据的方法

举报
nullptr_ 发表于 2024/03/04 14:23:18 2024/03/04
【摘要】 GaussDB(DWS)提供了非常多的函数用来造数，用于常规测试。

GaussDB(DWS)提供了非常多的函数用来造数，用于常规测试。

相关函数

日期，时间，间隔，时间计算等

  • now()
  • current_date
  • current_time
  • curtime() 带时区
  • curtime()::time without time zone 不带时区
  • to_timestamp(sec)
  • date_part(‘field’, source)
  • INTERVAL ‘1 day 10 second’
  • TIMESTAMP ‘2001-02-16 20:38:40’
  • age(timestamp, timestamp)
  • adddate(date, interval | int)
  • addtime(timestamp | time | text, interval | text)
  • subdate(date, interval | int)

uuid生成

  • uuid()
  • uuid_generate_v1()
  • sys_guid()

随机数生成

  • setseed(double precision) 为随后的随机设置种子
  • random()
  • rand()

随机字符串

  • md5(random()::text)

批量生成数字

  • range(1,100)
  • generate_series(1,100)
  • row_number()
  • rank()

批量生成时间

  • generate_series(now(), now()+ interval ‘1 day’, interval ‘1 second’)

取模

  • abs(mod(100,2))

用例

create table test(id int, now_time timestamp, name varchar, age float) with(orientation=column) distribute by hash(id);
insert into test select 1, now();
insert into test select 2, current_timestamp::TIMESTAMP(0);
insert into test select 3, to_timestamp(2);
insert into test select 4, TIMESTAMP '2001-02-16 20:38:40';
insert into test select 5, now()+ INTERVAL '10 day 10 second';
insert into test select 6, now(), date_part('year', now())::text;
insert into test select 7, now(), uuid();
insert into test select 8, now(), uuid_generate_v1();
insert into test select 9, now(), sys_guid();
insert into test select 10, now(), uuid(),random();
insert into test select 11, now(), generate_series(now(), now()+ interval '1 month', interval '1 day');
insert into test select generate_series(12,100), generate_series(now(), now()+ interval '1 month', interval '1 day'),md5(random()::text) ;

postgres=# select * from test order by 1;
 id  |          now_time          |                 name                 |      age       
-----+----------------------------+--------------------------------------+----------------
   1 | 2024-03-04 12:03:33.820218 |                                      |               
   2 | 2024-03-04 12:03:34        |                                      |               
   3 | 1970-01-01 08:00:02        |                                      |               
   4 | 2001-02-16 20:38:40        |                                      |               
   5 | 2024-03-14 12:03:43.910831 |                                      |               
   6 | 2024-03-04 12:03:33.93528  | 2024                                 |               
   7 | 2024-03-04 12:03:33.966333 | 65e54815-c1b9-0e00-1276-b77ac6d0fffe |               
   8 | 2024-03-04 12:03:33.999056 | 65e54816-03bf-0000-1277-b77ac6d0fffe |               
   9 | 2024-03-04 12:03:34.011676 | 65E54816321C00001278B77AC6D0FFFE     |               
  10 | 2024-03-04 12:03:34.02812  | 65e54816-76ac-0000-1279-b77ac6d0fffe | .4850455801934
  11 | 2024-03-04 12:03:35.182978 | 2024-03-04 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-15 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-19 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-05 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-16 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-18 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-06 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-20 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-21 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-07 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-22 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-23 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-08 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-24 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-25 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-09 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-26 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-10 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-27 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-28 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-11 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-29 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-30 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-12 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-31 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-04-01 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-13 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-04-02 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-04-03 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-14 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-03-17 12:03:35.182978+08        |               
  11 | 2024-03-04 12:03:35.182978 | 2024-04-04 12:03:35.182978+08        |               
  12 | 2024-03-12 12:03:51.39628  | 80654ac7f1191271e6c8ca75fe92b276     |               
  12 | 2024-03-22 12:03:51.39628  | 06dafef5d02701b9fc792abc5753c7d3     |               
  12 | 2024-03-20 12:03:51.39628  | 248fd8b916d2993e8d9bd634e30afe25     |               
  12 | 2024-03-28 12:03:51.39628  | 57757baf520a40b065b8003e8daee597     |               
  12 | 2024-03-24 12:03:51.39628  | 743da9190b457a692f5939d02acd1eec     |               
  12 | 2024-03-19 12:03:51.39628  | b93818c49f3142c631de885d9b5d3dbc     |               
  12 | 2024-04-02 12:03:51.39628  | 315c285e87ab131e7d85e0bdf2b4ec0a     |               
  12 | 2024-03-27 12:03:51.39628  | 328e35ad88a0d380041537156755d115     |               
  12 | 2024-03-17 12:03:51.39628  | 2f15525e52f79dfad662c727af923f0c     |               
  12 | 2024-03-25 12:03:51.39628  | 68b4c285b45960281c7ef4d1c5cf92fb     |               
  12 | 2024-03-06 12:03:51.39628  | b03843547cc25a94353227bceadcfaeb     |               
  12 | 2024-04-04 12:03:51.39628  | 09357ca099a02615cce4aaa4eb6535d2     |               
  12 | 2024-03-10 12:03:51.39628  | 16f3b6b35ae050eca7fcde328186ca76     |               
  12 | 2024-04-03 12:03:51.39628  | fd4d67aafff38d1a5586acb8b7083753     |               
  12 | 2024-03-26 12:03:51.39628  | 5e4e01d7c193b83b7f5e8f75705780d5     |               
  12 | 2024-03-18 12:03:51.39628  | 63db17942c18dc7882b67a79cd8a4555     |               
  12 | 2024-03-16 12:03:51.39628  | 764eddbddb00e934bdb23a513ddbf890     |               
  12 | 2024-03-30 12:03:51.39628  | d911b89436e70c25f395f2bee1adf147     |               
  12 | 2024-03-14 12:03:51.39628  | 81265074d2f6e3f1b6ca18a6f99f5ea5     |               
  12 | 2024-03-15 12:03:51.39628  | a5aa4135c61059a265d2175f176c399d     |               
  12 | 2024-03-29 12:03:51.39628  | af40a343683d630073b621041418d823     |               
  12 | 2024-03-09 12:03:51.39628  | 9f232a88da6f2a2dc83c0d51f1df8f9d     |               
  12 | 2024-03-11 12:03:51.39628  | 190d9b9d68b086bb43f53c56509172e2     |               
  12 | 2024-03-13 12:03:51.39628  | 4bf1f42a3188b649b9a74c5e9beae532     |               
  12 | 2024-03-07 12:03:51.39628  | c89ffd9bfa3b29eb73662326e91dbb8b     |               
  12 | 2024-03-05 12:03:51.39628  | 6354da38ea3f6f1852b6990fdcc23dc4     |               
  12 | 2024-03-21 12:03:51.39628  | 73a3b26be0dc9b1d35308d2ec5c66e6d     |               
  12 | 2024-03-23 12:03:51.39628  | 5f02735a0504e64e4d3ee175db00c0d2     |               
  12 | 2024-04-01 12:03:51.39628  | 3e33f7d2c1d85bc8c6ce2954aad62998     |               
  12 | 2024-03-08 12:03:51.39628  | f8bca07d9cf605b731a20cbbc1aff3ac     |               
  12 | 2024-03-31 12:03:51.39628  | 01bdd7c0d74f5b38157851a736c8988a     |               
  12 | 2024-03-04 12:03:51.39628  | df7455fcc0ee854a02e3bf7769b0d16b     |               
  13 | 2024-03-16 12:03:51.39628  | 7f7ab8cb379000953a244f191d29ce83     |               
  13 | 2024-03-17 12:03:51.39628  | 552d726339450083550cec47637716d0     |               
  13 | 2024-03-15 12:03:51.39628  | ed1038e799e3b425c4592f27eb33508e     |
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
云数据仓库 GaussDB(DWS)
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入