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 OverflowThere'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.
I just published a PostgreSQL port of the Open Source Shakespeare database: https://github.com/catherinedevlin/opensourceshakespeare. It's much more fun than all those other sample databases.
sql - Inserting dummy data into Postgresql - Stack Overflow
Is there a real-life Sample Database for PostgreSQL for beginner practice?
sql - Inserting dummy data into Postgres table - Stack Overflow
Sample PostgreSQL Database
I want to practice on something as close as possible to real-life database, where can I find such database?
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.
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.
I’m putting together some training on PostgreSQL. Does anyone know of a good ready-made sample database available for download? I can create one of course, but I don’t want to duplicate effort if something already exists. Was hoping there is something similar to the classic AdventureWorks used for SQL Server. Thanks!
INSERT INTO foo (name)
SELECT md5(random()::text) FROM generate_series(1, 1000);
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
IDENTITYcolumn
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