Re: PostgreSQL Gotchas

On Thu, Oct 06, 2005 at 05:47:36PM -0500, Jeffrey Melloy wrote:
> The only thing I could see actually being an issue is the random() one
> and add missing from.  The rest are trivial.  The random() thing is
> interesting, esoteric, and probably has never been a problem in a real
> situation.  (Or has exactly once, when he wrote that gotcha)

The random() issue has a workaround that the gotchas page doesn't
mention:

SELECT id, is_true
FROM (SELECT id, RANDOM() < 0.5 AS is_true FROM some_table OFFSET 0) AS t_tmp
WHERE is_true;

Tom Lane brought up the OFFSET 0 trick a couple of days ago in the
"Avoiding evaluating functions twice" thread:

http://archives.postgresql.org/pgsql-general/2005-10/msg00107.php

--
Michael Fuhr