Test data for PostgreSQL table

Question

How I can create test data into PostgreSQL Table using one SQL query? For example I would like to insert several lines of data into this table:

CREATE TABLE RELEASE_PLANNING(
 ID INTEGER NOT NULL,
 NAME TEXT,
 PLANNING_START_DATE DATE,
 PLANNING_END_DATE DATE,
 DESCRIPTION TEXT,
 LAST_UPDATE DATE,
 CREATED DATE
)
;

I tested this code

PreparedStatement ps = null;
        boolean committed = false;
        try
        {
            conn.setAutoCommit(false);

            for (int i = 0; i < 20; i++)
            {
                int randomNum = 10 + (int) (Math.random() * 20000);
                ps = conn.prepareStatement("INSERT INTO KNOWLEDGEBASE (ID, NAME) VALUES (?, generate_series(1,1000), md5(random()::text))");
                ps.setInt(1, randomNum);
//                ps.setString(2, "Test_file");
                ps.executeUpdate();

            }

            ps.close();

            conn.commit();
            committed = true;
        }

I get error

org.postgresql.util.PSQLException: ERROR: INSERT has more expressions than target columns Position: 7

The hard way, write several insert statements - with data that makes sense test-wise. — jarlh
– jarlh, Commented Apr 7, 2016 at 8:06
Ok, but I want to insert 1000 rows of random data. Is there any quick solution? — Peter Penzov
– Peter Penzov, Commented Apr 7, 2016 at 8:08
My standard trick is to insert 10 rows manually, then do insert into table select from table, with adjusted values. Do that a few times, and you have 1000 rows or more. — jarlh
– jarlh, Commented Apr 7, 2016 at 8:10
So, what happened when you tested the code you've added? What went wrong? What does it not do that you want it to? — IMSoP
– IMSoP, Commented Apr 7, 2016 at 8:53

Mike Sherrill 'Cat Recall' · Accepted Answer · 2016-04-07 10:45:59Z

The error message correctly tells you that you're trying to insert three columns of data (?, generate_series(1,1000), md5(random()::text))) into two columns (INSERT INTO KNOWLEDGEBASE (ID, NAME)). It's not clear what this has to do with your create table statement, which creates a table of a different name, having seven columns.

This kind of query will insert 100 rows.

insert into release_planning
select n, 'a', current_date, current_date, 'a', current_date, current_date
from generate_series (1, 100) n;

If you want random data, the best approach is probably to write some functions, and use them in the select clause of that statement. So, I might write

random_integer(low, high),
random_date(low, high), and
random_string(length),

and call them like this.

insert into release_planning
select n, 
       random_string(35), 
       current_date, 
       random_date(current_date, date '2016-12-31'), 
       random_string(20), 
       random_date(date (current_date + interval '3 days'), date '2016-12-31'), 
       current_date
from generate_series (1, 100) n;

AChernikov · Accepted Answer · 2023-07-03 22:05:47Z

1

Simple Data generation:

insert into release_planning
select n, 
       substr(concat(md5(random()::text), md5(random()::text)), 0, 35), 
       current_date, 
       current_date + (round(random()*100)::integer), 
       substr(concat(md5(random()::text), md5(random()::text)), 0, 20), 
       current_date + (round(random()*100)::integer),
       current_date + (round(random()*100)::integer)
from generate_series (1, 1000) n;

edited Jul 3, 2023 at 22:05

answered Jul 3, 2023 at 22:05

AChernikov

112 bronze badges

Collectives™ on Stack Overflow

Test data for PostgreSQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related