select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0
Date: 2016-08-08 18:12:47
Message-ID: CAADeyWgn0o_pNMjtzKFKXsEMY5Z5Oy8=M_OauTb1cKF-GRf2bg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good evening,

I wonder, why the following returns NULL and not 0 in 9.5.3?

# select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1);
array_length
--------------

(1 row)

# select array_length(array_remove(ARRAY[3,3,3],3), 1);
array_length
--------------

(1 row)

In a code for a word game (could be a card game too)
I remove played letter tiles from player's hand using
array_position and finally "compress" it using array_remove:

FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
LOOP
_letter := _tile->>'letter';
_value := (_tile->>'value')::int;
_col := (_tile->>'col')::int + 1;
_row := (_tile->>'row')::int + 1;

IF _value = 0 THEN
_pos = ARRAY_POSITION(_hand, '*');
ELSE
_pos = ARRAY_POSITION(_hand, _letter);
END IF;

IF _pos >= 1 THEN
_hand[_pos] := NULL;
ELSE
RAISE EXCEPTION 'Tile % not found in hand %',
_tile, _hand;
END IF;

_letters[_col][_row] := _letter;
_values[_col][_row] := _value;
END LOOP;

-- remove played tiles from player hand
_hand := ARRAY_REMOVE(_hand, NULL);
-- move up to 7 missing tiles from pile to hand
_hand_len := ARRAY_LENGTH(_hand, 1); -- OOPS can be NULL
_pile_len := ARRAY_LENGTH(_pile, 1); -- OOPS can be NULL
_move_len := LEAST(7 - _hand_len, _pile_len);
_hand := _hand || _pile[1:_move_len];
_pile := _pile[(1 + _move_len):_pile_len];

I understand that I have to wrap ARRAY_LENGTH calls
with COALESCE, but I am just curious why isn't 0 returned
in the first place...

Regards
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-08-08 18:19:58 Re: select array_length(array_remove(ARRAY[NULL,NULL,NULL],NULL), 1); returns NULL instead of 0
Previous Message Craig Boucher 2016-08-08 17:47:28 Column order in multi column primary key