Questions tagged [random]
The random tag has no usage guidance.
72 questions
22
votes
3
answers
10k
views
How can I generate a random bytea
I would like to be able to generate random bytea fields of arbitrary length (<1Gb) for populating test data.
What is the best way of doing this?
- 40.2k
19
votes
2
answers
26k
views
How to create column in DB with default value random string
Can I create column in DB table (PostgreSQL) which have default value random string, and how ?
If is not possible, please let me know that.
- 1,185
18
votes
3
answers
17k
views
Set random value from set
I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.
- 635
16
votes
3
answers
13k
views
Create hierarchy of multiple levels where each node has a random number of children
I need to create some test data that involves a hierarchy. I could make it easy and do a couple of CROSS JOINs, but that would give me a structure that is completely uniform / without any variation. ...
- 69.5k
9
votes
5
answers
10k
views
Get a truly RANDOM row from a PostgreSQL table quickly
I always used to do:
SELECT column FROM table ORDER BY random() LIMIT 1;
For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started ...
user200642
9
votes
2
answers
18k
views
Randomizing the Auto Increment in Mysql
We have a database, where we insert data into table whenever we receive an order from our services.
We use auto_increment to generate the ID and assign the order with the same.
This is usually same ...
- 203
8
votes
4
answers
7k
views
How do I do a complex GROUP BY in MySQL?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
- 799
7
votes
2
answers
7k
views
Perform TABLESAMPLE with WHERE clause in PostgreSQL
I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL.
This runs fine:
select * from customers tablesample bernoulli (1);
But I cannot figure out how ...
- 179
7
votes
2
answers
745
views
PostgreSQL randomising combinations with LATERAL
In the following example I have a table foo from which I'd like to pick out at random a row per group.
CREATE TABLE foo (
line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);
Let'...
- 1,730
6
votes
4
answers
6k
views
Primary key with randomly varying increments (so it cannot be guessed easily)
I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below:
- ...
5
votes
2
answers
3k
views
How to Randomly Select a Date?
I am using in a Bash Shell Script this code for retrieving a random date.
Is there a way to do that also in Microsoft SQL Server?
I can imagine that through a single SELECT query and probably by ...
user62547
5
votes
1
answer
5k
views
order by random meaning (postgresql)
One possible way to select random rows in PostgreSQL is this:
select * from table order by random() limit 1000;
(see also here.)
My question is, what does order by random() mean exactly? Is it that ...
- 151
5
votes
1
answer
2k
views
Generate fake data for several tables with relationships
I have 3 tables where I want to generate fake data in UsersCategoryLink.
How can I insert in the table UserCategoryLink the column UserID with random users from the table User and a random id from ...
- 930
5
votes
1
answer
2k
views
May postgresql's uuid_generate_v1() conflict when we change the computer date/time?
According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp:
https://www.postgresql.org/docs/9.4/static/uuid-ossp.html
On a distributed database scenario ...
- 467
5
votes
1
answer
6k
views
How do I make MySQL auto increment in random order?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
- 799
4
votes
2
answers
343
views
How did this statement update 3 rows in Postgres?
I am curious how this statement updated 3 rows in Postgres. All the other times I ran it, it would update 0 or 1. Is there a way to find out which rows?
bestsales=# update keyword set revenue = ...
- 226
4
votes
2
answers
13k
views
make random numbers for each row and column
I will edit some fake data in my database. But if I will make random numbers for each row and column, it is not so random as I want it. The result can you see on image below.
Here is my code I use:
...
- 930
4
votes
2
answers
5k
views
Sampling in PostgreSQL
I am looking for possible ways of random sampling in PostgreSQL. I found a couple of methods to do that with different advantages and disadvantages. The naive way to do that is:
select * from ...
- 43
4
votes
3
answers
8k
views
Select random 10 unique rows from MySQL database?
I have one table (questions) with 54 questions.
The students have to answer all 54 questions.
I have to show 10 questions at once, then next 10 and so on....
I have to select 10 questions ...
- 277
4
votes
1
answer
2k
views
Oracle random number as default column value
I'm trying to create a table in OracleDB and assign random number to one of the columns as a default. I have this setup working in postgresql
create table table_name (
column integer NOT NULL ...
- 41
4
votes
1
answer
536
views
Optimizing "random" choice in PostgreSQL
I have the following table
CREATE TABLE article (
id bigserial PRIMARY KEY,
text TEXT NOT NULL,
category_id bigint REFERENCES categories (id),
status TEXT NOT NULL,
locale TEXT ...
- 251
3
votes
3
answers
4k
views
How can I get satisfactory random numbers from the RAND function (or elsewhere)?
I'm creating a pseudo-random data set for application users to train on.
I'm surprised that if I seed the RAND() function with 1, 2, 3, etc., I get very nearly the same result from the seeded ...
- 750
3
votes
2
answers
3k
views
Use random string variable in a insert
This is one of my first tries to code something. I'm trying to develop a code, to add first name, last name, and date, randomly.
I have this for now:
declare @date date,
@val1 int,
@...
- 7,484
3
votes
1
answer
887
views
Set seed for multiple random numbers in postgres
The following will generate the same random number:
SELECT
setseed(0.5),
generate_series(1, 3) as id,
round(random()::decimal, 2) as random_number;
Which outputs something along the lines of :
...
- 316
3
votes
2
answers
3k
views
How to (optimally) get random sample of (a.id, b.id) pairs from two tables (a, b)?
Let's assume I have to very simple tables
CREATE TABLE a(id integer PRIMARY KEY,
t timestamp default now(),
sensor_readings real[]);
CREATE TABLE b(id integer PRIMARY KEY,
t ...
- 13.6k
3
votes
2
answers
288
views
Detect if `pg_crypto` in Postgres was built with strong random feature enabled
The source code for pg_crypto module mentions an option to build without cryptographically-strong random generation.
/*
Generate random bits. pg_backend_random() will do here, we don't promis
UUIDs ...
- 11k
2
votes
2
answers
7k
views
fill table A with random ID's from another table B
I have two tables and I want update the column GebruikerID from table Topic with the ID of the table Gebruiker with random values.
You must also know the follow things:
Topic.GebruikerID references ...
- 930
2
votes
2
answers
1k
views
Obtain different random() values with generate_series() in PostgreSQL
I am trying to generate random data (in volume) and was trying to use PostgreSQL's random() function with generate_series(), as shown:
SELECT
(SELECT random()) AS a,
(SELECT random()) AS b,
FROM ...
- 135
2
votes
2
answers
4k
views
postgresql random function returns duplicate results when order by random used in query
I'm using postgresql 11.1-1 on Windows 10. On my system, if I run a query like the following, I will get a different number for each random column:
postgres=# SELECT random() as sortOrder, random() ...
- 133
2
votes
3
answers
9k
views
how to replace user's names with random names?
How can I use SQL only on mysql DB to replace first and last names of real users in users table using a random name from two related tables: random_first_names and random_last_names. Our users table ...
- 131
2
votes
2
answers
1k
views
Selecting random rows from database initially while preserving that order later
I am building a quiz app, in which the student can login and can select the option of give exam. I want to show multiple choice questions to students randomly.
I stored all the questions in one ...
- 31
2
votes
3
answers
508
views
PSQL: create random data sets with the same statistic distrbution as another table
For test purposes, I want to create data sets (tables) where the statistic distribution of the values in the columns is the same as my actual data, but randomized.
Example : consider the following ...
- 311
2
votes
1
answer
201
views
Recommended Database(s) for Selecting Random Rows
I'm familiar with selecting random rows from a table in PostgreSQL. It's really slow. I've looked into some other databases and they all seem to be bad at it. Mostly because it's not a normal use case ...
- 181
2
votes
1
answer
511
views
What is the best way to divide data for random groups?
I would like to have persisted column which will randomly group data for 32 groups according to one of varchar key column in table. My idea was:
SELECT ABS(CAST(HASHBYTES('MD5',[keyColumnFromTable]) ...
- 119
2
votes
1
answer
2k
views
Duplicating rows based on a count column SQL Server [duplicate]
I have the following table:
MyTable
PlaceID NameID Code CodeCount
1 1 1234A 2
1 2 1234A 3
1 3 1234A 1
2...
2...
3...
3...
I want to select 1234A where ...
- 167
2
votes
1
answer
2k
views
Postgres - how to randomize two columns in a table
I have a table of User data which I would like to disguise/anonymize. Two of the columns have location data (e.g. lat and long, both BigDecimals).
I have anonymized all the names and other ...
- 123
2
votes
2
answers
559
views
How to update a foreign key with random values from the referenced table?
In order to generate test data for a Postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'.
How to do it?
I ...
2
votes
2
answers
224
views
Select random database records between subset of records having the same count(*) in a query
I have this simple query:
SELECT `value`, count(*) as `noHits`
FROM `topic`
WHERE identifier IN ('" . implode("','", array_keys($processedIdentifier)) ."')
GROUP BY `value`
...
- 21
2
votes
2
answers
4k
views
Update function to enter random/unique GUID per row
I am new to SQL and am trying to do the following.
i am using SQLite browser to edit the current file I have
I do have a SQLite database with around 1500 rows of data that I would like to create a ...
- 21
2
votes
1
answer
2k
views
Create a new table (multiple columns) by selecting random values from an existing table
I am stuck on the following task (PostgreSQL 9.3). Let's say we have the following table1 (which has 10k rows):
table1:
id
754
800
330
4
59
My goal is to create another table2 with two columns (...
- 21
1
vote
2
answers
12k
views
Postgresql. Generate sets of random integers
With this command I can generate 16384 random integers between 1 AND 200,000.
SELECT generate_series (1,16384),(random()*200000)::int AS id
I want to generate 10 sets of such integers. Each set must ...
- 1,012
1
vote
1
answer
2k
views
Generate random row order in a subquery
I know other answers here (and here) say to order by newid(). However if I am selecting top 1 in a subquery - so as to generate a random selection per row in the outer query - using newid() yields the ...
- 1,645
1
vote
1
answer
9k
views
Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL
I have this statement which generate a random string:
SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM ...
- 167
1
vote
2
answers
10k
views
how to select random and unique records from mysql (in php language)
I have a question table in my database which consists the following columns
question number which ranges from 1 to 150.
question text
text for 4 options as A,B,C,and D
answer option(either a,b,c or ...
1
vote
2
answers
4k
views
How to select random row with count()
Table names:
g_number name
123 John
123 Sara
123 Michael
I want to get one RANDOM name and quantity of all names with the same g_number in one query:
SELECT name, count(*) cnt
...
- 121
1
vote
1
answer
1k
views
How to auto-generate unique random-looking numbers?
Currently I'm passing one value to my database, it's a random and unique 10-digit number.
Is there a way I could generate this number directly in my database each time a new row is created?
It looks ...
- 11
1
vote
1
answer
805
views
mysql - select random rows with max 3 rows per parent id
I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
- 111
1
vote
2
answers
2k
views
Distinct Random select
Hi can anyone help with this little MS SQL Puzzle.
I basically need to get a 1 random answer from each of the submitters.
id, submitter, answer
1, James, Blue
2, James, Red
...
- 13
1
vote
1
answer
668
views
In Teradata SQL how to get random number between a pair, coming from field/column?
I am recently got stuck upon a simple problem in Teradata: how to get random number between a pair of number coming from field/column?
The function RANDOM( lower_bound , upper_bound ) apparently is ...
- 219
1
vote
2
answers
84
views
How we can design best optimized solution: Generate huge random numbers and check in database table whether this numbers are there or not
We have one table like:
Table Name:
random_number_series
Its columns are as below:
number [numeric(10)]
name [varchar(10)]
status [tinyint(1)]
type [tinyint(1)]
...
- 107