Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Integer array column having more than one value

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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)

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading