Skip to content

Instantly share code, notes, and snippets.

@marcocitus
Last active February 17, 2023 12:57
Show Gist options
  • Save marcocitus/dd315960d5923ad3f4d26b105618ed58 to your computer and use it in GitHub Desktop.
Save marcocitus/dd315960d5923ad3f4d26b105618ed58 to your computer and use it in GitHub Desktop.
Generate mock product data in PostgreSQL
CREATE TABLE IF NOT EXISTS words (
word text
);
CREATE TABLE IF NOT EXISTS product (
product_id int not null,
name text not null,
description text not null,
price decimal(12,2),
attributes jsonb,
primary key(product_id)
);
CREATE TABLE IF NOT EXISTS offer (
product_id int not null,
offer_id int not null,
seller_id int not null,
price decimal(12,2),
new bool,
primary key(product_id, offer_id)
);
CREATE OR REPLACE FUNCTION generate_products(num_products int)
RETURNS SETOF product AS $function$
DECLARE
all_words text[];
BEGIN
SELECT array_agg(word) INTO all_words FROM words;
RETURN QUERY
SELECT series AS product_id,
generate_text(all_words,3) AS name,
generate_text(all_words,50) AS description,
(100*random())::numeric(12,2) AS price,
generate_attributes(all_words,20) AS attributes
FROM generate_series(1,num_products) series;
END;
$function$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION generate_offers(num_offers int)
RETURNS SETOF offer AS $function$
SELECT series AS offer_id,
(random()*10000000)::int AS product_id,
(random()*10000)::int AS seller_id,
100*random()::decimal(12,2) AS price,
random()::int::bool AS new
FROM generate_series(1,num_offers) series;
$function$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION generate_attributes(words text[], num_attributes int)
RETURNS jsonb AS $function$
SELECT ('{'||string_agg(format('"%s":"%s"',
words[ceil(array_length(words,1)*random())],
words[ceil(array_length(words,1)*random())]),',') ||'}')::jsonb
FROM generate_series(1,num_attributes);
$function$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION generate_text(words text[], num_words int)
RETURNS text AS $function$
SELECT string_agg(words[ceil(array_length(words,1)*random())],' ')
FROM generate_series(1,num_words);
$function$ LANGUAGE sql;
@markons
Copy link

markons commented Jul 2, 2020

Hi marco(citus),

first, thanks for you fine sample. I've created the tables and the stored procedures in my (somewhat special i.e. mainframe) environment, and tried to generate some data into the tables with PostgreSQL code like this:

insert into product
(select generate_products(100)); <== PostgreSQL is somewhat specific; SELECT = EXEC(ute) stored procedure

I get following error message:

SQL Error [42804]: ERROR: column "product_id" is of type integer but expression is of type product
Hint: You will need to rewrite or cast the expression.
Position: 33

Do you have a hint please?

Regards
Gabor

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment