![]() ![]() Finally I’ve also used RANDOM() to be used later on when choosing a random User. I am pretty sure that this is not very efficient but it solves the problem. The trick I’ve used here was to cross join with the users table in order to get all possible combination of generate sequence and user_id. I’ve started creating a CTE called expanded to get a limited sequence of rows using GENERATE_SERIES, the same idea as before. To make it easier to understand I used a lot of CTE. This was a bit more complex to build than the previous one. This is a simple way to guarantee some rand number of posts per user. Also I’d like to perform some queries that return some random number of rows, so I’d like to chose a random User for each Post. The Post model is the first one which has a FOREIGN KEY, so I have to use real users.id values. This was easy, let’s move on and see how to choose random DB references. I’ve got strongly inspired by this amazing PG Cast - Generating Fake Email Addresses. I can also use RANDOM() function to build some random data in this case I am choosing the email domain from a finite list.ġ | | | | | | | | | | rows) Time: 5.159 ms Then the idea is to use the seq field given by the GENERATE_SERIES to have just unique values as I have this constraint. Note how easy is to generate a thousand or a million of rows just changing a single number. So I started this querying on GENERATE_SERIES(1, 10) as an easy way to generate 10 rows for me. In this case the only field that I need to generate is a VARCHAR one. ![]() I am using FOREIGN KEYS off course and NOT NULL as I want to have basic data validations on the DB level, as always. Note that all tables have an id SERIAL PRIMARY KEY column, so PostgreSQL will take care about generating the ids for me.Ī User has email, Post belongs to a User and it has title and Comment belongs to a Post and it’s made by another User and it has body column. CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR ( 40 ) NOT NULL UNIQUE ) CREATE TABLE posts ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users ( id ), title VARCHAR ( 100 ) NOT NULL UNIQUE ) CREATE TABLE comments ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users ( id ), post_id INTEGER NOT NULL REFERENCES posts ( id ), body VARCHAR ( 500 ) NOT NULL ) ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |