There's a PgFoundry project that contains several example PostgreSQL databases. Most of these haven't been updated for a while, but will still work with recent PostgreSQL versions.

If you need a bigger database, the MusicBrainz music metadata database has full database dumps available for download.

Answer from intgr on Stack Overflow
🌐
W3Schools
w3schools.com › postgresql › postgresql_create_demodatabase.php
PostgreSQL - Create Demo Databse
PostgreSQL CREATE TABLE PostgreSQL INSERT INTO PostgreSQL Fetch Data PostgreSQL ADD COLUMN PostgreSQL UPDATE PostgreSQL ALTER COLUMN PostgreSQL DROP COLUMN PostgreSQL DELETE PostgreSQL DROP TABLE Create Demo Database
Discussions

sql - Inserting dummy data into Postgresql - Stack Overflow
I'am creating a Postgres database and now I want to insert dummy data so I can learn to use ModelBI and other analysis tools. So far I've created some tables and found an automated way to insert th... More on stackoverflow.com
🌐 stackoverflow.com
Is there a real-life Sample Database for PostgreSQL for beginner practice?
https://wiki.postgresql.org/wiki/Sample_Databases That page lists various sources. More on reddit.com
🌐 r/PostgreSQL
7
7
February 12, 2024
sql - Inserting dummy data into Postgres table - Stack Overflow
I have got the following Postgres table: create table test ( id serial, contract varchar, amount0 int, amount1 int, price double precision ); I would like to insert 100 row... More on stackoverflow.com
🌐 stackoverflow.com
Sample PostgreSQL Database
You can try the one from the PostgreSQL tutorial site. http://www.postgresqltutorial.com/postgresql-sample-database/ There is also and AdventureWorks for Postgres: https://github.com/lorint/AdventureWorks-for-Postgres More on reddit.com
🌐 r/PostgreSQL
10
8
January 10, 2020
🌐
GitHub
github.com › morenoh149 › postgresDBSamples
GitHub - morenoh149/postgresDBSamples: Sample databases for postgres · GitHub
Sample databases for postgres. Contribute to morenoh149/postgresDBSamples development by creating an account on GitHub.
Starred by 553 users
Forked by 222 users
Languages   PLpgSQL
🌐
PostgreSQL Wiki
wiki.postgresql.org › wiki › Sample_Databases
Sample Databases - PostgreSQL wiki
Benchmarking databases such as DBT-2 or TPC-H can be used as samples.
🌐
UI Bakery
uibakery.io › sql-playground
Get a PostgreSQL instance with sample data in seconds | UI Bakery
Create a private PostgreSQL database with a predefined structure & test data in the shortest time. No local installations or spinning up AWS instances.
🌐
Medium
medium.com › geekculture › how-to-generate-dummy-data-in-postgresql-to-evaluate-query-performance-858733c7ab0f
How to Generate Dummy Data in Postgresql to Evaluate Query Performance? | by A. Gupta | Geek Culture | Medium
October 4, 2022 - Then here is good news for you, Postgres provides three different functions which you can use to replicate data across the horizontal and vertical levels. We will see each function in detail and how can you use these functions to create dummy data.
Find elsewhere
🌐
Sureshdsk
sureshdsk.dev › how-to-generate-dummy-data-in-postgres
How to generate dummy data in postgres? - DSK
April 8, 2021 - Lets create an employee table with id, name and salary columns and load some data, Create a dummy table CREATE TABLE public.employee ( id int8 NOT NULL, name varchar(120) NOT NULL, salary int8 NOT NULL, CONSTRAINT emp_pk PRIMARY KEY (...
🌐
GitHub
github.com › neondatabase › postgres-sample-dbs
GitHub - neondatabase/postgres-sample-dbs: A collection of sample Postgres databases for learning, testing, and development. · GitHub
A collection of sample Postgres databases for learning, testing, and development. - neondatabase/postgres-sample-dbs
Starred by 233 users
Forked by 41 users
Languages   PLpgSQL 99.8% | Shell 0.2%
🌐
OneCompiler
onecompiler.com › postgresql › 3zutqmumy
Dummy - PostgreSQL - OneCompiler
The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.
Top answer
1 of 2
2

You can randomly subscript the array of choices: (demo)

INSERT INTO test (contract,amount0, amount1, price)
SELECT (ARRAY['abc','klm','xyz']   )[(random()*2+1)::int],
       (ARRAY[50, 60, 80, 100, 200])[(random()*4+1)::int],
       (ARRAY[50, 60, 80, 100, 200])[(random()*4+1)::int],
       (ARRAY[1.5, 1.8, 2.1, 2.5]  )[(random()*3+1)::int]
FROM generate_series(1, 1e2, 1);

SELECT id, contract,amount0, amount1, price FROM test LIMIT 6;
id contract amount0 amount1 price
1 klm 50 100 2.1
2 xyz 100 100 1.5
3 abc 80 100 1.8
4 xyz 80 80 2.1
5 xyz 200 50 1.8
6 xyz 60 60 1.8

In PostgreSQL 16+, there's also array_sample(). Its intent is clearer, it looks a bit better, lets you sample multiple, non-consecutive elements. A potential downside is you can't make it repeatable with setseed(): (demo2)

INSERT INTO test (contract,amount0, amount1, price)
SELECT (array_sample(ARRAY['abc','klm','xyz']   ,1))[1],
       (array_sample(ARRAY[50, 60, 80, 100, 200],1))[1],
       (array_sample(ARRAY[50, 60, 80, 100, 200],1))[1],
       (array_sample(ARRAY[1.5, 1.8, 2.1, 2.5]  ,1))[1]
FROM generate_series(1, 1e2, 1);

Note that this does what you intended to do, making each selection independently random. In effect, it's quite likely that you'll get duplicate entries. If you prefer to keep getting unique combinations until you run out, and only then start over, you can do something similar to Mike's suggestion: (demo2)

INSERT INTO test (contract,amount0, amount1, price)
SELECT contract,amount0, amount1, price
FROM (values (50),(60),(80),(100),(200)) a(amount0),
     (values (50),(60),(80),(100),(200)) b(amount1),
     (values (1.5), (1.8), (2.1), (2.5)) c(price),
     (values ('abc'),('klm'),('xyz')   ) d(contract),
     generate_series(1, 1e2, 1) duplicator(n)
order by n, random()
limit 100;

Here's a function if you find yourself in need of a weighted random, meaning that you want to define exactly how likely an option is, compared to the others.


The reason why your solution didn't work is that since your scalar subqueries don't depend in any way on the outer query, they were evaluated only once and re-used. You can check the plan by running it with explain analyze verbose. You could trick the planner into thinking they somehow rely on the outer query by adding an outside reference, even if it does nothing (demo3) but the above does the same, in less code.

2 of 2
0

The problem is everytime you run your command PostgreSQL took a seed number and send it to srand function. That is why with the same seed you get same values from your arrays. Therefore, you need call function as many times you want another value from your arrays, and you can do that creating functions. That will cause context switch, but that is the only thing that I can think of. Maybe someone else can correct me. Here is the code:

create table test (
    id serial, 
    contract varchar, 
    amount0 int, 
    amount1 int, 
    price double precision
);

CREATE OR REPLACE FUNCTION random_amount()
  RETURNS int
  LANGUAGE sql VOLATILE PARALLEL SAFE AS
$func$
  SELECT ('[0:4]={50, 60, 80, 100, 200}'::int[])[trunc(random()  * (4 - 1 + 1) + 1)::int];
$func$;

CREATE OR REPLACE FUNCTION random_price()
  RETURNS int
  LANGUAGE sql VOLATILE PARALLEL SAFE AS
$func$
  (SELECT ('[0:3]={1.5, 1.8, 2.1, 2.5}'::float[])[trunc(random()  * (3 - 1 + 1) + 1)::int]);
$func$;

INSERT INTO test (amount0, amount1, price)
SELECT
  random_amount(), random_amount(), random_price()
FROM generate_series(1, 100);

select * from test;

Fiddle.

🌐
GitHub
gist.github.com › vwedesam › 3bacf88a0f8882a7903ff5500d7f974c
Generate dummy records in PostgreSQL · GitHub
If you want to generate 10,000 dummy records in PostgreSQL, you can use generate_series() in combination with INSERT.
🌐
Dataedo
dataedo.com › kb › databases › postgresql › sample-databases
Sample PostgreSQL databases - PostgreSQL - Design & Metadata
October 2, 2018 - Article for: PostgreSQL ▾ SQL Server Oracle database Snowflake Amazon Redshift SAP/Sybase ASE IBM DB2 MySQL MariaDB
🌐
Kaarel's corner
kmoppel.github.io › 2022-12-23-generating-lots-of-test-data-with-postgres-fast-and-faster
Generating lots of test data with Postgres, fast and faster
December 23, 2022 - How much time would it take to do a pg_dump or PITR restore clone from a future life-sized DB. Note though that for PITR test cases you can’t take the UNLOGGED performance optimization shortcut with pgbench, but need to push everything through WAL, making the process a bit slower. Everybody in the “data sphere” knows SQL, right? And if to add the PostgreSQL specific generate_series() sequence / row generation function into the formula, we have enough to generate a bit of dummy data!
🌐
Embarcadero
community.embarcadero.com › article › articles-database › 1076-top-3-sample-databases-for-postgresql
Community – Embarcadero RAD Studio, Delphi, & C++Builder Blogs
January 20, 2025 - Third party community sites based around Embarcadero tools and frameworks. English Discord – “Delphi Programmers” Discord group – independent but actively monitored b…
Top answer
1 of 2
3
INSERT INTO foo (name) 
  SELECT md5(random()::text) FROM generate_series(1, 1000);
2 of 2
5

Another method that can be used even when all columns have default expressions is to provide one column and use the OVERRIDING USER VALUE option in the INSERT in order to ignore those provided values.

insert into foo (id)
  OVERRIDING USER VALUE
select null
from generate_series(1, 10) as gs(i) ;

Tested in dbfiddle.uk (1)


One more method that uses MERGE - available only in Postgres version 15 (to be released next month). Thank you to Paul White who located the similar questions for SQL Server and Martin Smith and AndriyM for the answers:

  • how do I insert a default row?
  • Insert multiple rows into a table with only an IDENTITY column

Rewritten here with the necessary changes for Postgres and tested in dbfiddle.uk (2):

MERGE INTO
  foo AS tgt
USING
  generate_series(1, 10) AS src  -- << use your source row set here
ON
  FALSE
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES
;

(Wish note updated)
It would be nice if we could provide a 0-column table as input but this has not been implemented yet - the select works on its own but not the insert into foo ():

insert into foo ()
select
from generate_series(1, 10) as gs(i) ;

But this works since version 9.4. The trick is to use insert into foo - without the () - which accepts an input table with arbitrary number of columns (from zero up to the table's columns number):

insert into foo
select
from generate_series(1, 10) as gs(i) ;

Testing all three methods in dbfiddle.uk (3):

select version();
version
PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
SELECT 1
CREATE TABLE foo (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT DEFAULT md5(random()::text)
);
CREATE TABLE
insert into foo (id)
  OVERRIDING USER VALUE
select null
from generate_series(1, 3) as gs(i) ;   -- add 3 rows

select * from foo ;
INSERT 0 3
id name
1 08a153d31bafbef82d67c63b959ede87
2 5295cbb135445f954616fbf18031097a
3 5997bfd8e71c09bf244104797369c573
SELECT 3
MERGE INTO
  foo AS tgt
USING
  generate_series(1, 5) AS src   -- add 5 more rows
ON
  FALSE
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES ;

select * from foo ;
MERGE 5
id name
1 08a153d31bafbef82d67c63b959ede87
2 5295cbb135445f954616fbf18031097a
3 5997bfd8e71c09bf244104797369c573
4 73c644ac045b8dbdb4b17b2fb624538a
5 fb0ddc243709b3e3629838268ba5407f
6 af517dc84cb68ac0a5bd8c0cd2ef2ba5
7 fe33bf8a25bce0dbae0e8b80ff23af97
8 e35566ed57b7e0fe70878a03319fbb52
SELECT 8

Alternatively

MERGE INTO
  foo AS tgt
USING
  generate_series(1, 5) AS src   -- add 5 more rows
ON
  FALSE
WHEN NOT MATCHED THEN   
  INSERT (id) VALUES (DEFAULT);
insert into foo
select
from generate_series(1, 2) as gs(i) ;

select * from foo ;
INSERT 0 2
id name
1 08a153d31bafbef82d67c63b959ede87
2 5295cbb135445f954616fbf18031097a
3 5997bfd8e71c09bf244104797369c573
4 73c644ac045b8dbdb4b17b2fb624538a
5 fb0ddc243709b3e3629838268ba5407f
6 af517dc84cb68ac0a5bd8c0cd2ef2ba5
7 fe33bf8a25bce0dbae0e8b80ff23af97
8 e35566ed57b7e0fe70878a03319fbb52
9 6c71ffb4347d6593358cda63e802ae5e
10 a07ab009a6838533df51b02ca24d26fe
SELECT 10

fiddle