PostgreSQL Update – Array Syntax vs (?,?) Syntax for NULL Point Field

datatypespostgresqlpostgresql-9.3update

I'm using PostgreSQL 9.3.5.

Suppose I have the following table:

CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point);

I then insert a row, leaving pt NULL:

INSERT INTO point_test (name) VALUES ('me');

Then, I want to update pt using the array-like syntax:

UPDATE point_test SET pt[0] = 42, pt[1] = -42 WHERE name = 'me';

which appears to succeed (UPDATE 1) – EXPLAIN VERBOSE shows the following:

Update on pg_temp_65.point_test  (cost=0.00..11.75 rows=1 width=538)
  ->  Seq Scan on pg_temp_65.point_test  (cost=0.00..11.75 rows=1 width=538)
        Output: name, (pt[0] := 42::double precision)[1] := (-42)::double precision, ctid
        Filter: ((point_test.name)::text = 'me'::text)

However, pt is still NULL. If I use a slightly different syntax, it works in this case:

UPDATE point_test SET pt = '(42,-42)' WHERE name = 'me';

results in the point (42,-42) as expected.

Further, now that there is something in the field, I can update the point using the first syntax:

UPDATE point_test SET pt[0] = 84, pt[1] = -84 WHERE name = 'me';

results in the point (84,-84) as expected.

Why do the behaviors of the two syntaxes differ only when the pt column is NULL?

Best Answer

Per documentation:

It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1.

But geometric types are not arrays.
While the value as a whole can be NULL, parts of a point (or any geometric type) cannot.

All geometric types can be input as string literals and cast to the respective type. None of them accept NULL as part of the input. This results in an error message:

SELECT '(NULL,1)'::point;

ERROR: invalid input syntax for type point: "(NULL,1)"
LINE 1: SELECT '(NULL,1)'::point

On the other hand, if you try passing one or more NULL values to a constructor function, you get NULL for the whole value - which is a bit inconsistent, but still makes sense: if one component is unknown, the whole value is unknown. All of these tests return TRUE:

SELECT point(NULL, NULL) IS NULL
      ,point(1, NULL) IS NULL
      ,point(NULL, 1) IS NULL
      ,point(NULL, 1) IS NULL
      ,circle(point(1, 1), NULL) IS NULL;

This leaves the case where an UPDATE silently ignores an assignment of NULL to pt[0] or pt[1]. This should really do one of two things:

  • Set the point to NULL - like SELECT point(NULL,1) does.
  • Raise an exception - like SELECT '(NULL,1)'::point does.

I would say this is a bug and should be reported.

Related Question