pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Random function 3+ messages / 3 participants [nested] [flat]
* Random function @ 2020-03-24 18:10 Luis Roberto Weck <luisroberto@siscobra.com.br> 0 siblings, 2 replies; 3+ messages in thread From: Luis Roberto Weck @ 2020-03-24 18:10 UTC (permalink / raw) To: pgsql-performance@lists.postgresql.org Hi, I am trying to generate some random data using the random() function. However, I am getting the same result over mulitiple rows. This is a sample of the SQL I am using: select (select string_agg(random()::text,';') from pg_catalog.generate_series(1,3,1) ) from generate_series(1,10,1) And I am getting something like: |string_agg | +--------------------------------------------------------------+ |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| |0.243969671428203583;0.692578794434666634;0.291524752043187618| If this is the expected output, is there a way to always generate random numbers? ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Random function @ 2020-03-24 18:33 Tom Lane <tgl@sss.pgh.pa.us> parent: Luis Roberto Weck <luisroberto@siscobra.com.br> 1 sibling, 0 replies; 3+ messages in thread From: Tom Lane @ 2020-03-24 18:33 UTC (permalink / raw) To: Luis Roberto Weck <luisroberto@siscobra.com.br>; +Cc: pgsql-performance@lists.postgresql.org Luis Roberto Weck <luisroberto@siscobra.com.br> writes: > I am trying to generate some random data using the random() function. > However, I am getting the same result over mulitiple rows. This is a > sample of the SQL I am using: > select (select string_agg(random()::text,';') > from pg_catalog.generate_series(1,3,1) ) > from generate_series(1,10,1) The sub-select is independent of the outer select so it's only computed once, and then you get ten copies of that result. Restructuring the query, or inserting an artificial dependency on the outer select's data, would help. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Random function @ 2020-03-24 18:33 David G. Johnston <david.g.johnston@gmail.com> parent: Luis Roberto Weck <luisroberto@siscobra.com.br> 1 sibling, 0 replies; 3+ messages in thread From: David G. Johnston @ 2020-03-24 18:33 UTC (permalink / raw) To: Luis Roberto Weck <luisroberto@siscobra.com.br>; +Cc: pgsql-performance@lists.postgresql.org How is this a performance related question? On Tue, Mar 24, 2020 at 11:10 AM Luis Roberto Weck < luisroberto@siscobra.com.br> wrote: > However, I am getting the same result over mulitiple rows. This is a > sample of the SQL I am using: > > select (select string_agg(random()::text,';') > from pg_catalog.generate_series(1,3,1) ) > from generate_series(1,10,1) > > And I am getting something like: > > |string_agg | > +--------------------------------------------------------------+ > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > |0.243969671428203583;0.692578794434666634;0.291524752043187618| > > If this is the expected output, Yes, you've asked it to compute a value, assign it to a column, then generate 10 rows of that value. is there a way to always generate random > numbers? > Don't use a scalar subquery in the main target list. One possible answer: select format('%s;%s;%s', random(), random(), random()) from generate_series(1, 10) David J. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-03-24 18:33 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-03-24 18:10 Random function Luis Roberto Weck <luisroberto@siscobra.com.br> 2020-03-24 18:33 ` Tom Lane <tgl@sss.pgh.pa.us> 2020-03-24 18:33 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox