https://supabase.com/ logo
#sql
Title
# sql
s

stibbs

11/07/2021, 10:58 PM
Can someone point me to a good method for creating dummy data with postgresql? I have a table like so
Copy code
sql
CREATE TABLE jobs (
  id uuid DEFAULT uuid_generate_v4 () primary key,
  invoice_id uuid references public.invoices not null,
  user_id uuid references auth.users not null,
  application_url text,
  business_name text not null,
  content_description text not null,
  content_how_to_apply text not null,
  content_who text not null,
  job_location text,
  job_type text not null,
  title text not null,
  timezone text,
  work_style text not null,
  valid_until timestamp with time zone,
);
The confusing bit to me is that in this table you either populate job
job_location
OR
timezone
depending on the value in
work_style
I have no idea if I'm on the right path, but so far I have:
Copy code
sql
insert into public.jobs (
  invoice_id,
  user_id,
  application_url,
  business_name,
  content_description,
  content_how_to_apply,
  content_who,
  job_location,
  job_type,
  title,
  timezone,
  work_style,
  valid_until
)
select 
  'manually created invoice_id',
  'manually created user_id',
  'https://www.google.com',
  MD5(random()::text), -- business_name
  MD5(random()::text), -- content_description
  MD5(random()::text), -- content_how_to_apply
  MD5(random()::text), -- content_who
  -- how do i make some populate job_location?
  'ft', -- job_type
  MD5(random()::text), -- title
  -- how do i make some populate timezone?
  -- work_style needs to be 'remote' or 'flex'
  now() + interval '5 day'
from generate_series(1, 100);
g

garyaustin

11/07/2021, 11:48 PM
If you can put some values for each field in an array this link might help select a random one ... https://stackoverflow.com/questions/14299043/postgresql-pl-pgsql-random-value-from-array-of-values
s

stibbs

11/07/2021, 11:59 PM
Sweet that will get me the
work_style
. Something like
Copy code
sql
select (array['remote', 'flex'])[floor(random() * 2 + 1)];
Although I'm not sure how to insert that into my generate query... It works
g

garyaustin

11/08/2021, 12:03 AM
should get you timezone and and job locations too
s

stibbs

11/08/2021, 12:04 AM
Don't I need an
if
or something to do that?
timezone
is populated when
work_style
=
remote
job_location
is populated when
work_style
=
flex
g

garyaustin

11/08/2021, 12:04 AM
I'm not really reading your whole thing, just though about random arrays as helping. Sorry
s

stibbs

11/08/2021, 12:05 AM
Thanks though, you've got me one step closer 😄
Actually maybe I should just do what you've suggested
g

garyaustin

11/08/2021, 12:06 AM
you can do if () then blah end if;
s

stibbs

11/08/2021, 12:06 AM
My app will only use one of them based on work_style, it doesn't really matter if both are populated
Copy code
sql
insert into public.jobs (
  invoice_id,
  user_id,
  application_url,
  business_name,
  content_description,
  content_how_to_apply,
  content_who,
  job_location,
  job_type,
  title,
  timezone,
  work_style,
  valid_until
)
select 
  'manually created invoice_id',
  'manually created user_id',
  'https://www.google.com',
  substr(md5(random()::text), 0, 15), -- business_name
  MD5(random()::text), -- content_description
  MD5(random()::text), -- content_how_to_apply
  MD5(random()::text), -- content_who
  -- how do i make some populate job_location?
  'ft', -- job_type
  upper(substr(md5(random()::text), 0, 25)), -- title
  -- how do i make some populate timezone?
  (array['remote', 'flex'])[floor(random() * 2 + 1)],
  now() + interval '5 day'
from generate_series(1, 100);
This makes sense, but it requires referring to a column that doesn't exist yet? If that makes sense...
ws
only exists after query execution?
Copy code
sql
select
  substr(md5(random()::text), 0, 25) as title,
  (array['remote', 'flex'])[floor(random() * 2 + 1)] as ws,
  case when ws = 'remote' then 'aest'
    else null
  end
from generate_series(1, 10);
column "ws" does not exist
g

garyaustin

11/08/2021, 12:18 AM
maybe do the if around the random array result then have 2 statements to set ws and other stuff in the then, else does what ever else needs to be done. That only works for this limited case of a few options thought.
s

stibbs

11/08/2021, 12:19 AM
Would that also require changes to my planned
insert into ... values
?
g

garyaustin

11/08/2021, 12:21 AM
basically then you are just doing an if on random and then several things based on result where you would assign the values. I'll be honest, I don't have time to really dig into it more, like I said just knew about get random array values.
s

stibbs

11/08/2021, 12:21 AM
Yeah no worries, thank you very much 🙂
g

garyaustin

11/08/2021, 12:23 AM
good luck