How to count records of an array with condition in PostgreSQL

Question

i have e.g. a table "Employee" with

last_name :: character varying
handed_texts :: text[]

Where text is an own data-type with "("Date", title/not_exist)".

There could be x entries while x := ({}, 1,2,...)

Example:

jones , {"(2017-01-01, stars_in_univ)", "(2017-01-01, not_exist)",   "(2017-01-02, unemployed ants)"}
tomes , {"(2017-01-01, not_exist)", "(2017-01-08, shared_minds)"}

My Problem now: i want to count the existing texts for each name. I tried e.g. something like that:

SELECT last_name, handed_texts,
       CASE
           WHEN handed_texts IS NULL THEN 0
           ELSE
               FOREACH i IN ARRAY handed_texts
               DECLARE c integer;
               SET c = 0;
               LOOP
                   IF i NOT LIKE '%not_exist%' THEN c+1 ELSE c END IF
                 return c
              END LOOP
      END AS counted_texts
FROM employee

I know the declaration is totally wrong, but i couldn't find any way to declare a variable in a way that my system accepts so i just wrote it at the place i think it should be declared. The other way i tried to count was to make a new array where i just put the correct/wanted record in and use array_length, but that seems to be even more catastrophal than the e.g above.

It's not a good idea to define your own data type with the same name as an existing built-in one — user330315
– user330315, Commented Jan 25, 2017 at 10:35
Thx, that's right. But i found for a short example nothing good so fast. Maybe just call it protocol with same definition ... . — Xayer
– Xayer, Commented Jan 25, 2017 at 10:57

Hambone · Accepted Answer · 2017-01-25 11:01:14Z

1

I can't promise it's efficient, but if you unnest and re-aggregate, it seems this would be possible:

with exploded as (
  select last_name, unnest (handed_texts) as handed
  from employee
)
select
  last_name, array_agg (handed) as handed_texts
from exploded
where
  handed not like '%not_exist%'
group by last_name

That said, if you changed handed_texts to its own table like this:

create table handed_texts (
   last_name varchar,
   title_date date,
   title_name text
)

And did a join on last_name, I think it might provide a cleaner solution all around.

answered Jan 25, 2017 at 11:01

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

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

4 Comments

Xayer Over a year ago

It seems to be a good idea, but the/my problem here is, the possibility of unknown. so i should make a CASE where to fill a 0 for {} in. That will mybe work. Thx. And yes, it would be a good idea to change that like you wrote, but it is given and i have to work/deal with it ... .

Hambone Over a year ago

Try the suggestion on your data and let me know. Again, I don't promise it's fast, but I do think it's scalable.

Xayer Over a year ago

I figgured out the problem for now: The UNNEST does work, no Problem. Then: last_name handed jones (2017-01-01,stars_in_univ) jones (2017-01-01,not_exist) jones (2017-01-02,unemployed ants) tomes (2017-01-01,not_exist) tomes (2017-01-08, shared_minds) when i now use WHERE handed NOT LIKE '%not_exist%' there is the ERROR operator does not exist: boolean !~~ unknown at character XXX HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts It does not really help me at the moment. But thx so far

Hambone Over a year ago

That's curious... can you post the DDL for your table? You said that the handed_texts column is text[] -- an array of text, right? When you run the subquery (with the unnest) in PgAdmin, what does it list as the datatype for the column named handed? I don't see how any of my code could produce a boolean!

McNets · Accepted Answer · 2017-01-25 11:15:08Z

0

You can convert it to string, and the use standard LIKE construction:

select last_name, count(*) 
from (select last_name, array_to_string(handed_texts,',','') t
      from employee e2) a
where a.t like '%not_exist%
group by last_name;

Sorry, Missed in translation from my rextester

edited Jan 25, 2017 at 11:15

answered Jan 25, 2017 at 10:39

McNets

10.9k3 gold badges39 silver badges70 bronze badges

1 Comment

Xayer Over a year ago

SELECT last_name, handed_texts, count() as num FROM (SELECT last_name, handed_texts, array_to_string(handed_texts,',','') t FROM employee) a WHERE a.t NOT LIKE '%not_exist%' GROUP BY last_name, handed_texts, count() as num My count is always 1. But i found my mistake at the example and question above and make a better example with the handed_texts - array and make the question more precise. First: at the array there can be x entries x := ({}, 1,2,....) I need the count of the handed_texts for each name itself.

Roman Hocke · Accepted Answer · 2017-01-25 11:35:52Z

0

The unnest function may be useful here. You can use it like this:

SELECT e.id, COUNT(texts.id)
FROM employee AS e
LEFT JOIN (
    -- this "unfolds" all arrays as if they were a single table of all texts of all employees
    SELECT id, UNNEST(handed_texts) AS handed_texts
    FROM employee
) AS texts ON texts.id = e.id
WHERE ... -- any condition on single array element being now in texts "table"
GROUP BY e.id

edited Jan 25, 2017 at 11:35

answered Jan 25, 2017 at 11:29

Roman Hocke

4,2411 gold badge22 silver badges36 bronze badges

1 Comment

Xayer Over a year ago

Thx so far. I get the ERROR: operator does not exist: texts !~~ unknown. It seems to be the same ERROR like i got with Hambones answer.

Collectives™ on Stack Overflow

How to count records of an array with condition in PostgreSQL

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related