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?

  • 1
    I presume this is because there's a difference between null and a point having both components being null. – Colin 't Hart Aug 11 '14 at 8:55
  • Please re-run with RETURNING * on both, e.g. UPDATE ... RETURNING *, and update the question. I tend to agree with Colin though. – Craig Ringer Aug 11 '14 at 9:04
  • 1
    @Colin'tHart That's what I initially thought too, but try: SELECT point(NULL, NULL) IS NULL;. It's true. – Craig Ringer Aug 11 '14 at 9:05
  • 2
    It might be a bug. At least it looks like an inconsistency that's worth to be reported as such. The doc says we can subscript point with the array-like syntax, but in a super-concise way with no mention of updates or special cases for nulls. – Daniel Vérité Aug 11 '14 at 15:11
  • 1
    @Colin'tHart: The point is that components of (currently existing) geometric types cannot be NULL. Only the whole value can. – Erwin Brandstetter Aug 11 '14 at 19:10

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:

      ,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.

| improve this answer | |
  • In my question, pt[0] and pt[1] are both set in the same statement (i.e. pt would be valid and non-null) - could you imagine a third case where the UPDATE succeeds? – gws Aug 27 '14 at 23:57
  • Obviously, you cannot UPDATE´ *parts* of a geometric data type that is NULL` - even if you update all parts. You would have to update the whole value as one to make it non-null. And not sure I understand your last question in the comment. – Erwin Brandstetter Aug 28 '14 at 0:08
  • I was just referring to the two bullet points at the end of your answer, and imagining a third case wherein a valid assignment to pt succeeded. My thinking was that the context of a statement or transaction could allow an update of pt "as one," despite being written piecemeal using array syntax, but I see your point. – gws Aug 28 '14 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.