Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Сustomize behavior of NULLS in array functions #6808

Open
izveigor opened this issue Jun 30, 2023 · 0 comments
Open

Сustomize behavior of NULLS in array functions #6808

izveigor opened this issue Jun 30, 2023 · 0 comments
Labels
enhancement New feature or request

Comments

@izveigor
Copy link
Contributor

izveigor commented Jun 30, 2023

Is your feature request related to a problem or challenge?

Follow on to #6662

No response

Describe the solution you'd like

array_append

postgres=# select array_append(array[1, 2, 3], NULL);
 array_append 
--------------
 {1,2,3,NULL}
(1 row)

postgres=# select array_append(NULL, 1);
 array_append 
--------------
 {1}
(1 row)

postgres=# select array_append(array[]::integer[], 1);
 array_append 
--------------
 {1}
(1 row)

postgres=# select array_append(array[]::integer[], NULL);
 array_append 
--------------
 {NULL}
(1 row)

postgres=# select array_append(NULL, NULL);
 array_append 
--------------
 {NULL}
(1 row)

array_cat

postgres=# select array_cat(NULL, array[1]);
 array_cat 
-----------
 {1}
(1 row)

postgres=# select array_cat(array[1], NULL);
 array_cat 
-----------
 {1}
(1 row)

postgres=# select array_cat(array[]::integer[], array[1]);
 array_cat 
-----------
 {1}
(1 row)

postgres=# select array_cat(array[1], array[]::integer[]);
 array_cat 
-----------
 {1}
(1 row)

postgres=# select array_cat(array[]::integer[], array[]::integer[]);
 array_cat 
-----------
 {}
(1 row)

postgres=# select array_cat(NULL, NULL);
 array_cat 
-----------
 
(1 row)

array_dims

postgres=# select array_dims(array[]::integer[]);
 array_dims 
------------
 
(1 row)

postgres=# select array_dims(NULL);
ERROR:  could not determine polymorphic type because input has type unknown

array_fill

postgres=# select array_fill(1, NULL);
ERROR:  dimension array or low bound array cannot be null

postgres=# select array_fill(NULL, array[1]);
ERROR:  could not determine polymorphic type because input has type unknown

postgres=# select array_fill(NULL, NULL);
ERROR:  could not determine polymorphic type because input has type unknown

postgres=# select array_fill(1, array[]::integer[]);
 array_fill 
------------
 {}
(1 row)

postgres=# select array_fill(array[]::integer[], array[1]);
ERROR:  could not find array type for data type integer[]

postgres=# select array_fill(array[]::integer[], array[]::integer[]);
ERROR:  could not find array type for data type integer[]

array_length

postgres=# select array_length(array[1], NULL);
 array_length 
--------------
             
(1 row)

postgres=# select array_length(NULL, 1);
ERROR:  could not determine polymorphic type because input has type unknown

postgres=# select array_length(NULL, NULL);
ERROR:  could not determine polymorphic type because input has type unknown

postgres=# select array_length(array[]::integer[], NULL);
 array_length 
--------------
             
(1 row)

postgres=# select array_length(array[]::integer[], 1);
 array_length 
--------------
             
(1 row)

postgres=# select array_length(array[]::integer[], array[]::integer[]);
ERROR:  function array_length(integer[], integer[]) does not exist
LINE 1: select array_length(array[]::integer[], array[]::integer[]);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

array_ndims

postgres=# select array_ndims(NULL);
ERROR:  could not determine polymorphic type because input has type unknown

postgres=# select array_ndims(array[]::integer[]);
 array_ndims 
-------------
            
(1 row)

array_position

postgres=# select array_position(NULL, 1);
 array_position 
----------------
               
(1 row)

postgres=# select array_position(NULL, 1, 2);
 array_position 
----------------
               
(1 row)

postgres=# select array_position(array[1, 2, 3], NULL);
 array_position 
----------------
               
(1 row)

postgres=# select array_position(array[1, 2, 3], NULL, 2);
 array_position 
----------------
               
(1 row)

postgres=# select array_position(array[1, 2, 3, NULL], NULL);
 array_position 
----------------
              4
(1 row)

postgres=# select array_position(array[1, 2, 3, NULL], NULL, 2);
 array_position 
----------------
              4
(1 row)

postgres=# select array_position(array[1, 2, 3], 1, NULL);
ERROR:  initial position must not be null

postgres=# select array_position(array[]::integer[], 1);
 array_position 
----------------
               
(1 row)

postgres=# select array_position(array[]::integer[], 1, 1);
 array_position 
----------------
               
(1 row)

postgres=# select array_position(array[]::integer[], NULL);
 array_position 
----------------
               
(1 row)

postgres=# select array_position(array[]::integer[], NULL, 2);
 array_position 
----------------
               
(1 row)

array_positions

postgres=# select array_positions(array[1, 2, 3], NULL);
 array_positions 
-----------------
 {}
(1 row)

postgres=# select array_positions(array[1, 2, 3, NULL], NULL);
 array_positions 
-----------------
 {4}
(1 row)

postgres=# select array_positions(NULL, 1);
 array_positions 
-----------------
 
(1 row)

postgres=# select array_positions(NULL, NULL);
 array_positions 
-----------------
 
(1 row)

postgres=# select array_positions(array[]::integer[], 1);
 array_positions 
-----------------
 {}
(1 row)

postgres=# select array_positions(array[]::integer[], NULL);
 array_positions 
-----------------
 {}
(1 row)
                                                        ^
postgres=# select array_positions(array[]::integer[], array[]::i
nteger[]);
ERROR:  function array_positions(integer[], integer[]) does not exist
LINE 1: select array_positions(array[]::integer[], array[]::integer[...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

array_prepend

postgres=# select array_prepend(1, NULL);
 array_prepend 
---------------
 {1}
(1 row)

postgres=# select array_prepend(NULL, array[1, 2, 3]);
 array_prepend 
---------------
 {NULL,1,2,3}
(1 row)

postgres=# select array_prepend(NULL, NULL);
 array_prepend 
---------------
 {NULL}
(1 row)

postgres=# select array_prepend(1, array[]::integer[]);
 array_prepend 
---------------
 {1}
(1 row)

postgres=# select array_prepend(array[]::integer[], array[1, 2, 3]);
ERROR:  function array_prepend(integer[], integer[]) does not exist
LINE 1: select array_prepend(array[]::integer[], array[1, 2, 3]);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

postgres=# select array_prepend(array[]::integer[], array[[1], [2], [3]]);
ERROR:  function array_prepend(integer[], integer[]) does not exist
LINE 1: select array_prepend(array[]::integer[], array[[1], [2], [3]...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
postgres=# select array_prepend(array[]::integer[], array[array[]::integer[], array[]::integer[], array[]::integer[]]);
ERROR:  function array_prepend(integer[], integer[]) does not exist
LINE 1: select array_prepend(array[]::integer[], array[array[]::inte...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

postgres=# select array_prepend(1, array[]::integer[]);
 array_prepend 
---------------
 {1}
(1 row)

postgres=# select array_prepend(1, NULL);
 array_prepend 
---------------
 {1}
(1 row)

array_remove

postgres=# select array_remove(array[1, 2, 3], NULL);
 array_remove 
--------------
 {1,2,3}
(1 row)

postgres=# select array_remove(array[1, 2, 3, NULL], NULL);
 array_remove 
--------------
 {1,2,3}
(1 row)

postgres=# select array_remove(NULL, 1);
 array_remove 
--------------
 
(1 row)

postgres=# select array_remove(NULL, NULL);
 array_remove 
--------------
 
(1 row)
                                 ^
postgres=# select array_remove(array[]::integer[], 1);
 array_remove 
--------------
 {}
(1 row)

postgres=# select array_remove(array[]::integer[], NULL);
 array_remove 
--------------
 {}
(1 row)

postgres=# select array_replace(NULL, 1, 2);
 array_replace 
---------------
 
(1 row)

postgres=# select array_replace(array[1, 2, 3], NULL, 2);
 array_replace 
---------------
 {1,2,3}
(1 row)

postgres=# select array_replace(array[1, 2, 3, NULL], NULL, 2);
 array_replace 
---------------
 {1,2,3,2}
(1 row)

postgres=# select array_replace(array[1, 2, 3, NULL], 1, NULL);
  array_replace  
-----------------
 {NULL,2,3,NULL}
(1 row)

postgres=# select array_replace(array[1, 2, 3, NULL], NULL, NULL);
 array_replace 
---------------
 {1,2,3,NULL}
(1 row)

postgres=# select array_replace(NULL, NULL, NULL);
 array_replace 
---------------
 
(1 row)

postgres=# select array_replace(array[]::integer[], 1, 2);
 array_replace 
---------------
 {}
(1 row)

array_to_string

postgres=# select array_to_string(NULL, ',');
ERROR:  could not determine polymorphic type because input has type unknown
postgres=# select array_to_string(NULL, ',', '*');
ERROR:  could not determine polymorphic type because input has type unknown
postgres=# select array_to_string(array[1, 2, 3], NULL);
 array_to_string 
-----------------
 
(1 row)

postgres=# select array_to_string(array[1, 2, 3, NULL], NULL, NULL);
 array_to_string 
-----------------
 
(1 row)

postgres=# select array_to_string(array[1, 2, 3, NULL], NULL, '*');
 array_to_string 
-----------------
 
(1 row)

postgres=# select array_to_string(array[1, 2, 3, NULL], );
ERROR:  syntax error at or near ")"
LINE 1: select array_to_string(array[1, 2, 3, NULL], );
                                                     ^
postgres=# select array_to_string(array[1, 2, 3, NULL], ',', NULL);
 array_to_string 
-----------------
 1,2,3
(1 row)

postgres=# array_to_string(array[]::integer[], ',', '*');
ERROR:  syntax error at or near "array_to_string"
LINE 1: array_to_string(array[]::integer[], ',', '*');
        ^
postgres=# select array_to_string(array[]::integer[], ',', '*');
 array_to_string 
-----------------
 
(1 row)

trim_array

postgres=# select trim_array(array[1, 2, 3], NULL);
 trim_array 
------------
 
(1 row)

postgres=# select trim_array(NULL, 2);
ERROR:  could not determine polymorphic type because input has type unknown
postgres=# select trim_array(NULL, NULL);
ERROR:  could not determine polymorphic type because input has type unknown
postgres=# select trim_array(array[]::integer[], NULL);
 trim_array 
------------
 
(1 row)

postgres=# select trim_array(array[]::integer[], 2);
ERROR:  number of elements to trim must be between 0 and 0
postgres=# select trim_array(array[]::integer[], 0);
 trim_array 
------------
 {}
(1 row)

Describe alternatives you've considered

No response

Additional context

No response

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

1 participant