Skip site navigation (1)
Skip section navigation (2)
## SELECT '(1, nan, 3)'::cube

### pgsql-bugs by date

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[])); ...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

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