Andre Radke <lists@spicynoodles.net> writes:
> testdb=# CREATE TABLE boxarray_test (col1 BOX[2]);
> testdb=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}');
> testdb=# SELECT * FROM boxarray_test;
> col1
> ---------------
> {(4,4),(2,2)}
> (1 row)
I've finished looking into this, and the short answer is that your input
is not syntactically correct. Because type box has typdelim = ';', the
correct input would have been
INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)";"(4,4),(2,2)"}');
(btw, you could omit the double-quote marks here.) There is indeed a
bug here: since the array parser didn't think the comma was an item
delimiter, IMHO it should have considered the array to contain one item
(3,3),(1,1),(4,4),(2,2)
which would have provoked an error when handed to the box-datatype input
parser. Instead the array parser messed up and passed only the second
double-quoted substring to the box input routine.
I have fixed this for 7.3: with the just-committed code, I get
boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}');
ERROR: Bad box external representation '(3,3),(1,1),(4,4),(2,2)'
boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)";"(4,4),(2,2)"}');
INSERT 533436 1
boxes=# INSERT INTO boxarray_test VALUES ('{(3,3),(1,1);(4,4),(2,2)}');
INSERT 533437 1
boxes=# select * from boxarray_test;
col1
---------------------------
{(3,3),(1,1);(4,4),(2,2)}
{(3,3),(1,1);(4,4),(2,2)}
(2 rows)
This still leaves us with the question of whether it's really a good
idea that type box has typdelim ';' and not ',' like everything else
uses. Anyone have a strong feeling about changing it or not? If we
change it, we'd instead get this behavior:
boxes=# update pg_type set typdelim = ',' where typname = 'box';
UPDATE 1
boxes=# select * from boxarray_test;
col1
-------------------------------
{"(3,3),(1,1)","(4,4),(2,2)"}
{"(3,3),(1,1)","(4,4),(2,2)"}
(2 rows)
boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}');
INSERT 533438 1
and the double quotes would be required.
One argument against changing is that it'd break pg_dump output for
existing tables containing arrays of boxes ... if any there be.
Given that this hasn't come up before, I wonder if anyone's using 'em.
regards, tom lane
