[GENERAL] Random numbers
Hello list, I want to generate random numbers in Pl/pgSQL. How can I do this? To be more specific, I have to generate random numbers among 0 and 5. Thanks in advance. Regards, Karel Riverón Student Scientific Council Informatics Science University http://www.uci.cu
Re: [GENERAL] Random numbers
On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar kesco...@estudiantes.uci.cu wrote: I want to generate random numbers in Pl/pgSQL. How can I do this? To be more specific, I have to generate random numbers among 0 and 5. One way: select n from unnest(ARRAY[0,1,2,3,4,5]) n order by random() limit 1;
Re: [GENERAL] Random numbers
2013/5/26 bricklen brick...@gmail.com: On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar kesco...@estudiantes.uci.cu wrote: I want to generate random numbers in Pl/pgSQL. How can I do this? To be more specific, I have to generate random numbers among 0 and 5. One way: select n from unnest(ARRAY[0,1,2,3,4,5]) n order by random() limit 1; somewhat shorter: SELECT (random() * 5)::INT Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Random numbers
Thanks bricklen. I found another way in PostgreSQL 9.1.0 Documentation. SELECT * INTO random_number FROM random(); -- random function gives me a random value among 0.0 -- and 1.0 SELECT * INTO rounded_number FROM round(random_number * 5); -- this line is completly obviously. Saludos, Karel Riverón Consejo Científico Estudiantil Universidad de las Ciencias Informáticas - Original Message - | From: bricklen brick...@gmail.com | To: Karel Riveron Escobar kesco...@estudiantes.uci.cu | Cc: pgsql-general@postgresql.org | Sent: Saturday, May 25, 2013 11:50:03 AM | Subject: Re: [GENERAL] Random numbers | On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar | kesco...@estudiantes.uci.cu wrote: | | I want to generate random numbers in Pl/pgSQL. How can I do this? | | | To be more specific, I have to generate random numbers among 0 and | | 5. | | One way: | select n from unnest(ARRAY[0,1,2,3,4,5]) n order by random() limit 1; http://www.uci.cu
Re: [GENERAL] Random numbers
On 25 May 2013 17:56, Ian Lawrence Barwick barw...@gmail.com wrote: 2013/5/26 bricklen brick...@gmail.com: On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar kesco...@estudiantes.uci.cu wrote: I want to generate random numbers in Pl/pgSQL. How can I do this? To be more specific, I have to generate random numbers among 0 and 5. One way: select n from unnest(ARRAY[0,1,2,3,4,5]) n order by random() limit 1; somewhat shorter: SELECT (random() * 5)::INT I'd rather use something like: SELECT floor(random()*6)::INT as this gives uniform results distribution. Compare the two below queries, in the first you have twice less results for 0 and 5 than for the rest numbers. WITH x AS (SELECT (random()*5)::INT r FROM generate_series(1,1000*1000)) SELECT r, count(*) FROM x GROUP BY r ORDER BY r ; WITH x AS (SELECT floor(random()*6)::INT r FROM generate_series(1,1000*1000)) SELECT r, count(*) FROM x GROUP BY r ORDER BY r ; regards Szymon
Re: [GENERAL] Random numbers
How would be if I would want to generate values among 3 and 5? Regards, Karel Riverón Student Scientific Council Informatics Science University - Original Message - | From: Szymon Guz mabew...@gmail.com | To: Ian Lawrence Barwick barw...@gmail.com | Cc: bricklen brick...@gmail.com, Karel Riveron Escobar | kesco...@estudiantes.uci.cu, pgsql-general@postgresql.org | Sent: Saturday, May 25, 2013 12:10:52 PM | Subject: Re: [GENERAL] Random numbers | On 25 May 2013 17:56, Ian Lawrence Barwick barw...@gmail.com | wrote: | | 2013/5/26 bricklen brick...@gmail.com : | | | | | | On Sat, May 25, 2013 at 8:45 AM, Karel Riveron Escobar | | | kesco...@estudiantes.uci.cu wrote: | | | | | | I want to generate random numbers in Pl/pgSQL. How can I do | | this? | | | To be more specific, I have to generate random numbers among 0 | | and | | 5. | | | | | | | | | One way: | | | select n from unnest(ARRAY[0,1,2,3,4,5]) n order by random() | | limit | | 1; | | | somewhat shorter: | | | SELECT (random() * 5)::INT | | I'd rather use something like: | SELECT floor(random()*6)::INT | as this gives uniform results distribution. | Compare the two below queries, in the first you have twice less | results for 0 and 5 than for the rest numbers. | WITH x AS (SELECT (random()*5)::INT r FROM | generate_series(1,1000*1000)) SELECT r, count(*) FROM x GROUP BY r | ORDER BY r ; | WITH x AS (SELECT floor(random()*6)::INT r FROM | generate_series(1,1000*1000)) SELECT r, count(*) FROM x GROUP BY r | ORDER BY r ; | regards | Szymon http://www.uci.cu
Re: [GENERAL] Random numbers
On 25 May 2013 18:14, Karel Riveron Escobar kesco...@estudiantes.uci.cuwrote: How would be if I would want to generate values among 3 and 5? Hi Karel, try something like: SELECT floor(3 + random()*(5-3+1))::INT Or generally: CREATE OR REPLACE FUNCTION random_range(INTEGER, INTEGER) RETURNS INTEGER AS $$ SELECT floor(($1 + ($2 - $1 + 1) * random()))::INTEGER; $$ LANGUAGE SQL; and then: SELECT random_range(3,5); regards Szymon
[GENERAL] 9.1: Slow to add range check on indexed column
I just created a new partition in a log table, and then built a check constraint on the recorded timestamp column. I have an index on this column, but the adding of the check constraint took a very, very long time. It seems that PostgreSQL 9.1 does not check the index when building a check constraint on an indexed column. Is this correct? Is this addressed in future releases? Thanks. -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] 9.1: Slow to add range check on indexed column
On 25/05/2013 20:37, Moshe Jacobson wrote: I just created a new partition in a log table, and then built a check constraint on the recorded timestamp column. I have an index on this column, but the adding of the check constraint took a very, very long time. It seems that PostgreSQL 9.1 does not check the index when building a check constraint on an indexed column. Is this correct? Is this addressed in future releases? At a guess, I'd imagine that the check constraint has to be checked against every row in the table, so the index would not be used anyway. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
