Skip site navigation (1) Skip section navigation (2)

SELECT '(1, nan, 3)'::cube

From: Robert Brewer <robert(dot)brewer(at)yougov(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: SELECT '(1, nan, 3)'::cube
Date: 2011-03-15 15:57:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
Hi folks,

I'm working on a hypercube implementation in Postgres using contrib/cube 
and need to insert 80,000 rows in one go from Python. Doing so with 
INSERT, even multiple statements in one call, is pretty slow. I've been 
investigating if using COPY is faster. It is, but there's a problem: 
some of the cubes should include 'NaN'::float. Writing:

    INSERT INTO foo (coords) VALUES (cube(ARRAY[1, 'nan', 3]::float[])); fine. But I can't find the magic incantation to do the same 
thing using COPY FROM. Indeed, even SELECT chokes on this:

     # SELECT '(1, nan, 3)'::cube;
     ERROR:  bad cube representation
     LINE 1: select '(1, nan, 3)'::cube;
     DETAIL:  syntax error at or near "n"

Note that the 'cube' datatype also doesn't have a 'binary' repr, and 
COPY FROM exhibits the same problem with CSV as it does with text.

I actually found out I could trick the parser into storing 'Inf' by just 
giving it a very large number, like '(1, 1e+309, 3)::cube', but I can't 
figure out an equivalent trick for NaN.

Also note that 'nan' and 'inf' are not a problem for float[]

     # select '{1, nan, inf}'::float[];
     (1 row)

...but 1e+309 raises an error for float (where it does not for cube):

     # select '{1, 1e+309, 3}'::float[];
     ERROR:  "1e+309" is out of range for type double precision
     LINE 1: select '{1, 1e+309, 3}'::float[];


  1. It would be good to have cube parsing in PG behave more like float, 
allowing 'nan' and 'inf', and once those are working, deny using large 
numbers to fake the parser into returning Infinity.
  2. If anyone can think of a way to trick the cube parser in the short 
term into returning NaN, I'd love to hear it.
  3. If anyone understands cubeparse.c better than I do and can shoot me 
a quick patch, I'd appreciate it.

Robert Brewer

pgsql-bugs by date

Next:From: Robert BrewerDate: 2011-03-15 16:08:42
Subject: SELECT '(1, nan, 3)'::cube;
Previous:From: macDate: 2011-03-15 14:23:06
Subject: BUG #5931: password when setup

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group