[PostgreSQL] 간단하게 테스트용 랜덤 데이터 생성하는 법

postgres 에서 간단하게 테스트 데이터를 생성하는 방법입니다

1 ~ 1000 까지 데이터 생성

오라클의 connect by level과 비슷합니다

> create table sk1.t1 as SELECT LEVEL FROM GENERATE_SERIES(1,1000) level 

1000 row(s) modified.


> SELECT * FROM sk1.t1 

level|
-----+
1|
2|
3|
4|
5|
6|


> SELECT count(*) FROM sk1.t1 

count|
-----+
1000|

1 row(s) fetched.

랜덤값 데이터 생성

> CREATE TABLE sk1.t2 AS 
    SELECT
        gs as idx,
        '테스트 문자열' || gs AS test_string,
        md5(random()::text) AS random_string
    FROM
        generate_series(1, 1000000) AS gs

1000000 row(s) modified.


> SELECT * FROM sk1.t2 

idx|test_string|random_string                   |
---+-----------+--------------------------------+
  1|테스트 문자열1   |9d10985621ee6d8b7854da291515193f|
  2|테스트 문자열2   |5036f19f56394cbe1ef2de3085d6f100|
  3|테스트 문자열3   |a34f2ea67b151576181e7e3f374fc257|
  4|테스트 문자열4   |10dc8ff615af9e69804b4889a1d37f6f|
  5|테스트 문자열5   |cc69e21fa6141be00fe69121b3fcac02|
  6|테스트 문자열6   |0a0afd9c16670738fa5adee6b681650d|
  7|테스트 문자열7   |58400d249dbc76fecef8d1555590332e|
  8|테스트 문자열8   |fa31708ba491084160c219cb5aae9ee3|
  9|테스트 문자열9   |313b748b5408f3edf26d94ed036da799|
 10|테스트 문자열10  |746c20ad02ce9dd390eb564b69b56cb2|
 11|테스트 문자열11  |f559a44c0bfcf747fadd480561853e38|



> SELECT count(*) FROM sk1.t2 

count  |
-------+
1000000|

1 row(s) fetched.

1시간 단위 데이터 생성

시간을 기준으로 데이터를 만드는 예제 입니다. 시작 시각, 종료 시각, 생성 간격을 입력해 주세요

  
> CREATE TABLE sk1.t3 AS 
SELECT
(ROW_NUMBER() OVER()) as idx,
md5(random()::text) AS random_string,
gs AS test_date
FROM
generate_series('2025-01-01 00:00'::timestamp, '2025-01-01 23:00'::timestamp, '1 hours') AS gs

24 row(s) modified.


> SELECT * FROM sk1.t3 

idx|random_string                   |test_date              |
---+--------------------------------+-----------------------+
1|f48a63af5f74f25f6ada7698478a6aad|2025-01-01 00:00:00.000|
2|130f90d11a9174cb2f9613a4788f633e|2025-01-01 01:00:00.000|
3|602785391498d2a28593331a5209e7d0|2025-01-01 02:00:00.000|
4|eb257701ddfcc18b8d24d9d3c05ee333|2025-01-01 03:00:00.000|
5|951f715d840c17cae72b326d9cd0a358|2025-01-01 04:00:00.000|
6|8a4319a74f00c4ecbb57dfd617438505|2025-01-01 05:00:00.000|
7|3fcb1c8cdf9c67715f333263911e2604|2025-01-01 06:00:00.000|
8|77385d88b9ce40b4a4d9c9d982231847|2025-01-01 07:00:00.000|
9|30546ff3d46b096b7fb629a578581493|2025-01-01 08:00:00.000|
10|cf28876768658e0d4dd379d243782d49|2025-01-01 09:00:00.000|
11|1ce7a1a996af545c2bae9b51f92ed726|2025-01-01 10:00:00.000|
12|2b57f3d190121486d02176f76a75f5ce|2025-01-01 11:00:00.000|
13|25b88fc7a06c0aa0291cae641090f5db|2025-01-01 12:00:00.000|
14|c747e7f8303a4ce3371ffe28515a712f|2025-01-01 13:00:00.000|
15|465d7af41930483bf90e7346f8f0ba38|2025-01-01 14:00:00.000|
16|4abf7a0cf2b09c7ff0aaa1e4049de9e9|2025-01-01 15:00:00.000|
17|189af748218dceca0a4ccddb1a774335|2025-01-01 16:00:00.000|
18|cdcfee5e6cf1d64e0fe7f2c031e98504|2025-01-01 17:00:00.000|
19|a7ddf3b21827c0fff3433e8d50b914a0|2025-01-01 18:00:00.000|
20|b4a0464c8245db9ac499555121f0093d|2025-01-01 19:00:00.000|
21|f84cbecbe03d7654c6100a86702b91b7|2025-01-01 20:00:00.000|
22|d32d222aaf910020bd7d602ef49d718b|2025-01-01 21:00:00.000|
23|aebc80606da74807b204812b4dff7c5f|2025-01-01 22:00:00.000|
24|25e363824eef1d973752ecbedc02579f|2025-01-01 23:00:00.000|

24 row(s) fetched.