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