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

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

From: "Robert Brewer" <fumanchu(at)aminus(dot)org>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: SELECT '(1, nan, 3)'::cube;
Date: 2011-03-15 16:08:42
Message-ID: F1962646D3B64642B7C9A06068EE1E64110D759F@ex10.hostedexchange.local (view raw or flat)
Thread:
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. Writing:

    INSERT INTO foo (coords) VALUES (cube(ARRAY[1, 'nan', 3]::float[]));

...works 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[];
       float8
     -----------
      {1,NaN,Infinity}
     (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[];

So:

  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
fumanchu(at)aminus(dot)org


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-03-15 17:07:16
Subject: Re: SELECT '(1, nan, 3)'::cube;
Previous:From: Robert BrewerDate: 2011-03-15 15:57:46
Subject: SELECT '(1, nan, 3)'::cube

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