Integer array column having more than one value

Question

This column is an integer array type:

log_session
-----------------------
 {105683,105694}
 {111833}
 {120285}
 {108592}
 {84659,84663}

I want to know how many log_session have just 1 value (3 in this case), and how many have more than 1 value (2 here).

EDIT:

select 
    count(*) filter(where array_length(log_session, 1) = 1) as cnt_length_1,
    count(*) filter(where array_length(log_session, 1) > 1) as cnt_length_more_than_1
from mytable;

ERROR:  function array_length(integer[]) does not exist
LINE 2:     count(*) filter(where array_length(log_session) = 1) as ...
                                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

select version();
                                                               version                                                               
-------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.20 on x86_64-pc-linux-gnu (Ubuntu 9.6.20-1.pgdg18.04+1), compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)

GMB · Accepted Answer · 2023-03-22 17:20:52Z

2

You can use array_length:

select 
    count(*) filter(where array_length(log_session, 1) = 1) as cnt_length_1,
    count(*) filter(where array_length(log_session, 1) > 1) as cnt_length_more_than_1
from mytable

cnt_length_1	cnt_length_more_than_1
3	2

fiddle

answered Mar 22, 2023 at 17:20

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

arilwan Over a year ago

Ah, I guess this fuction is only available in recent versions of PGSQL. Currently I have access to a remote, older version PostgreSQL 9.6.20 on x86_64-pc-linux-gnu.

arilwan Over a year ago

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

GMB Over a year ago

@arilwan: array_length was already available in version 9.6: postgresql.org/docs/9.6/functions-array.html. However, the filter clause of aggregate funtions such as count() was not. Probably you need to rephrase the counts, as in: sum(case when array_length(log_session, 1) = 1 then 1 else 0 end) as cnt_length_1

Zegarek Over a year ago

CASE-based filter clause emulation should work too, but it shouldn't be necessary as filter support reaches all the way back to 9.4. OP's edit isn't a clean paste because it first shows a copy of your (correct) code, but then the error message suggests they skipped the array_length(anyarray, int) second argument (dimension) when they ran it: "filter(where array_length(log_session) = 1)".

Collectives™ on Stack Overflow

Integer array column having more than one value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related