Thursday, December 1, 2011

Quick and dirty test data generation


I am a noob with postgres and linux, if you are an expert why are you reading this? So I just had to generate a large ammount (>1) of rows to test against. This is a step by step dirty way to get from SELECT to INSERT (I know you can do it with sql smart ass but can you do it in under 2 minutes?)

1) Get the table with some random row of data
psql -d <my-db> -c "SELECT * FROM <table> where id = <id>" > raw_sql
gedit raw_sql&
(GEDIT? I hear you scream. NOOB! I scream back.)

2) Format the raw_sql. Depends on the data your millage will vary.
Find and replace whitespace with nothing.
Find and replace || with | null |
Find and replace || with |
Find and replace | with ,
Save as <file>.csv

3) Delete the null stuff.
Open a spreadsheet and import the csv.
Select all columns with null and delete them.
(Basically the spreadsheet allows values and attribute names to be aligned and easily deleted.)
Export to csv.

4) Create the Insert
Open the new csv file (using gedit)
Add at the top psql -d <my-db> -c "INSERT INTO <table>
Then surround the attribute list with parens
Then add below that VALUES.
Then surround the values with parens and add a final " at the end.

5) Multiply
Copy and paste as many times as necessary and change the values that you want to differ manually.
Save as <something>.sh

And run ./<something>.sh

Till next time,
Stratos out.

No comments:

Post a Comment