faker_fdw is a foreign data wrapper for PostgreSQL that generates fake data.
You can generate data for testing, create examples for your blog, populate a development database and other things related to generate fake data.
When faker_fdw is installed, create one milion rows on a people table is as easy as doing:
devdb=> INSERT INTO public.people SELECT ssn, name, phone_number FROM faker.people;In the snippet above, faker.people is a foreign table that returns some "person" fields, but you can do much more,
like geolocation, addresses, credit card and so on.
It's easy, once faker_fdw was installed (see below how to install and create the faker_srv), just create
a foreign table with fields like ssn, name, first_name, last_name, address,
phone_number and many others. The magic is done by faker-factory, a Python library that generate code through providers. For example, ssn, name, first_name, last_name are generated by person provider, while address is generated by address provider.
We'll start with ssn, name and address:
guedes=> CREATE FOREIGN TABLE fake.person (ssn varchar, name varchar, address text)
SERVER faker_srv OPTIONS (max_results '100');
CREATE FOREIGN TABLE
Time: 36,400 ms
guedes=> SELECT * FROM fake.person limit 10;
ssn | name | address
-------------+--------------------+--------------------------------------
452-53-4113 | Jordin McClure PhD | 61875 Bernhard Lights Apt. 594 +
| | Shonnamouth, FL 19690-6384
586-60-9538 | Isabela D'Amore | 622 Williamson Road +
| | Schmidtside, MH 44962
525-45-7125 | Irving Terry | 30478 Cummings Turnpike +
| | New Chazstad, SC 67727-1963
314-36-1089 | Janette Bradtke | 73775 Janell Bridge Apt. 120 +
| | Lonzofort, MH 88220
382-65-0182 | Dayna Lesch | 71914 Mosciski Fords +
| | Lake Dalechester, FM 08869-8100
698-15-6164 | Judie Dickens | 7634 Leuschke Burgs +
| | West Antonio, MD 76638-0668
870-44-9100 | Brooks Stroman | 63236 Pfannerstill Junction Apt. 308+
| | South Shea, MS 34801-5187
652-43-1400 | Kendrick Denesik | 0077 Runolfsdottir Cape +
| | Lake Gaynell, RI 42511
879-63-4746 | Osie Kemmer | 4343 Jazlynn Knoll +
| | Lake Trueport, AL 88273
837-30-7043 | Nyasia Smitham | 3043 Gretta Shoal +
| | New Haiden, UT 08099-9192
(10 rows)
Time: 17,276 msNow, lets suppose that you want the phone_number, that's easy, just add
the column:
guedes=> ALTER FOREIGN TABLE fake.person ADD COLUMN phone_number varchar;
ALTER FOREIGN TABLE
Time: 31,200 ms
guedes=> SELECT * FROM fake.person LIMIT 10;
ssn | name | address | phone_number
-------------+-------------------------+--------------------------------+--------------------
520-61-0046 | Miss Nan Hilll DDS | 1162 Jaron Mill Apt. 435 +| 635.024.1809x351
| | East Isham, UT 99699-8045 |
554-47-6145 | Ayden Jenkins DVM | 41357 McKenzie Skyway +| 528.396.8357
| | Port Warren, NM 35237 |
021-55-5151 | Dr. Randolf McClure PhD | 9738 Prince Corners Suite 091 +| 696.074.2586x2173
| | Harmonhaven, AK 67018 |
441-09-6518 | Harlene Hoppe | 858 Lenard Port +| 07969004580
| | Kristinafurt, GU 59690 |
486-27-1135 | Dr. Amalie Parker | 5581 Feil Summit Apt. 736 +| 00554249871
| | West Tatiahaven, PR 12346-7661 |
681-31-4609 | Louis Aufderhar I | 216 Hessel Valley Apt. 891 +| (818)010-0501x1646
| | North Dorothea, RI 12275-4420 |
419-81-4064 | Lila Koch DVM | 354 Fay Vista Suite 603 +| 907-143-1119
| | Greenfelderburgh, MI 50297 |
308-50-3314 | Toby Shields | 059 Nitzsche Parks +| 383.998.7283x035
| | East Daunte, VT 76481 |
405-92-2887 | Delwin Lynch | 467 Carrol Stream Apt. 466 +| +94(9)7970982195
| | Corimouth, AS 08861 |
651-62-6328 | Shantell Pfeffer DVM | 60971 Nya Villages Suite 939 +| 599.631.2393
| | Mamiestad, GU 54537-9334 |
(10 rows)
Time: 19,201 msThe data are randomly generated, so each execution even in different sessions will generate a new random dataset, but, sometimes, this is not what you want, because your tests must re-run using the same set of data. You can persist data in other tables, rather than select directly from faker tables, or you can set a seed option and use the same seed in different sessions to get the same set of data, like the following example:
guedes=> alter foreign table fake.person options ( add seed '1234' );
ALTER FOREIGN TABLE
guedes=> \c - postgres
You are now connected to database "guedes" as user "postgres".
guedes=# select * from fake.person limit 5;
ssn | name | address | phone_number
-------------+-----------------------+---------------------------------+----------------------
165-12-0147 | Dr. Aron Lind IV | 2564 Julius View +| 409.956.0720x73661
| | South Amon, MO 14305 |
160-16-9547 | Rowan Bauch | 8474 Thompson Lights +| 05197913028
| | Schultzburgh, CT 41584 |
356-05-2862 | Jaydon Bogisich | 90204 Sim River Suite 583 +| 663.781.9218x7740
| | Langworthchester, FM 38547-5316 |
864-54-9210 | Maximillian Stamm PhD | 8213 Julie Path +| 1-803-066-7124x72838
| | Port Whitmouth, DC 03464 |
721-72-6607 | Foster Schimmel | USS Ullrich +| (423)625-9466
| | FPO AA 79263-9218 |
(5 rows)
Time: 123.969 ms
guedes=# \c - guedes
You are now connected to database "guedes" as user "guedes".
guedes=> select * from fake.person limit 5;
ssn | name | address | phone_number
-------------+-----------------------+---------------------------------+----------------------
165-12-0147 | Dr. Aron Lind IV | 2564 Julius View +| 409.956.0720x73661
| | South Amon, MO 14305 |
160-16-9547 | Rowan Bauch | 8474 Thompson Lights +| 05197913028
| | Schultzburgh, CT 41584 |
356-05-2862 | Jaydon Bogisich | 90204 Sim River Suite 583 +| 663.781.9218x7740
| | Langworthchester, FM 38547-5316 |
864-54-9210 | Maximillian Stamm PhD | 8213 Julie Path +| 1-803-066-7124x72838
| | Port Whitmouth, DC 03464 |
721-72-6607 | Foster Schimmel | USS Ullrich +| (423)625-9466
| | FPO AA 79263-9218 |
(5 rows)
Time: 115.262 msSome fields accept options and they are passed to the respective provider. Let's suppose that you
want some kind of "date and time on this year" for your person table, so you can add a field
date_time_this_year like below:
guedes=# alter foreign table fake.person add column date_time_this_year timestamp;
ALTER FOREIGN TABLE
guedes=# select date_time_this_year as d from fake.person limit 5;
d
---------------------
2018-07-28 02:14:46
2018-07-08 00:22:27
2018-12-01 09:35:49
2018-12-31 06:18:34
2018-02-19 16:22:58
(5 registros)That's fine, but what if you want only datetimes after now? The date time provider
supports parameters like before_now and after_now, so let's use them:
guedes=# alter foreign table fake.person alter column date_time_this_year options (after_now 'True');
ALTER FOREIGN TABLE
guedes=# select date_time_this_year as d from fake.person where date_time_this_year > now();
d
---------------------
2018-09-19 21:37:21
2018-10-10 18:19:15
2018-12-21 10:39:42
2018-10-16 14:04:07
2018-12-04 19:20:27
2018-12-28 13:18:59
2018-09-30 11:45:49
2018-11-13 21:58:59
2018-10-11 05:49:17
2018-10-23 20:47:17
guedes=# alter foreign table fake.person alter column date_time_this_year options (before_now 'False');
ALTER FOREIGN TABLE
guedes=# select date_time_this_year as d from fake.person limit 5;
d
---------------------
2018-12-26 01:16:32
2018-11-22 05:59:49
2018-12-14 23:18:47
2018-11-10 10:08:14
2018-09-17 21:38:14
(5 registros)
guedes=# select date_time_this_year as d from fake.person where date_time_this_year<now();
d
---
(0 registro)
You must install a few dependencies related to PostgreSQL and
Python: multicorn, fake-factory then fake_fdw.
In Debian this is as easy as:
sudo apt-get install postgresql-XX-python-multicorn
sudo pip install Faker
sudo pip install https://github.com/guedes/faker_fdw/archive/v0.2.4.zipIf you are using the postgres docker image https://hub.docker.com/_/postgres
sudo apt-get install postgresql-XX-python3-multicorn
sudo pip3 install Faker
sudo pip3 install https://github.com/guedes/faker_fdw/archive/v0.2.4.zipOnce packages was installed, choose which database you want faker_fdw by typing:
CREATE EXTENSION multicorn;
CREATE SERVER faker_srv
FOREIGN DATA WRAPPER multicorn
OPTIONS (wrapper 'faker_fdw.FakerForeignDataWrapper');faker_fdw supports IMPORT SCHEMA that is used to create example tables for many
providers. For some limitations all fields are created as varchar but you can use
ALTER TABLE to change field type and there is no semantic between fields in the
same record, that's it, each columns area independently generated.
To import an example schema:
IMPORT FOREIGN SCHEMA fake
FROM SERVER faker_srv
INTO fake
OPTIONS ( locale 'pt_BR', max_results '100');
uedes=# \det fake.
Lista de tabelas externas
Esquema | Tabela | Servidor
---------+--------------+-----------
fake | address | faker_srv
fake | automotive | faker_srv
fake | bank | faker_srv
fake | barcode | faker_srv
fake | color | faker_srv
fake | company | faker_srv
fake | credit_card | faker_srv
fake | currency | faker_srv
fake | date_time | faker_srv
fake | file | faker_srv
fake | internet | faker_srv
fake | isbn | faker_srv
fake | job | faker_srv
fake | lorem | faker_srv
fake | misc | faker_srv
fake | person | faker_srv
fake | phone_number | faker_srv
fake | profile | faker_srv
fake | python | faker_srv
fake | ssn | faker_srv
fake | user_agent | faker_srvAnd that's it! Have fun!
faker_fdw is released under the PostgreSQL License.
See LICENSE file for information.