I want to build a sql that generates a 2 column table where the first are series of timestamps and the second is a functon that provides values. For example here is random(). But the random function give me always the same value for the whole table, I want different. How to fix it?

SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
                              '2008-03-05 12:00',
                              '1 day') as ts, random() as value;

Generated table:

"2008-03-01 00:00:00";0.816707271616906
"2008-03-02 00:00:00";0.816707271616906
"2008-03-03 00:00:00";0.816707271616906
"2008-03-04 00:00:00";0.816707271616906
"2008-03-05 00:00:00";0.816707271616906

1 Answers

5
a_horse_with_no_name On

Move the random() call to the select list:

SELECT ts.*, random() as value
FROM generate_series('2008-03-01 00:00'::timestamp,
                     '2008-03-05 12:00',
                     '1 day') as ts;

By putting it into the FROM clause it's only evaluated once.