1

I have the following table named jobs:

id PK Serial
model String
model_id UNSIGNED Integer
status String
created_at timestamp
prosessed_at timestamp

And foreach model Id I record each status in an array:

select
 model_id,
 model,
 array_agg(id) as jobs,
 array_agg(statuses) as statuses
from jobs
group by model,model_id

And I use that as a subquery in order to detect any malfucntion in prosessed jobs:

select
 *
from (
  select
   model_id,
   model
   array_agg(id) as jobs,
   array_agg(statuses) as statuses
  from jobs
  group by model,model_id
) as jobs
where
    'aborted' in statuses
and
    'pending' not in statuses
and
    'failed' not in statuses
and
    'processed' not in statuses;

But in the following line:

    'aborted' in statuses

Has some sort syntax error:

SQL Error [42601]: ERROR: syntax error at or near "statuses"
  Position: 312

Do you have any idea why?

1

2 Answers 2

2

You have a number of syntax errors in your query:

  • Missing comma after model
  • The base column is called status not statuses
  • You can't write in statuses, you need to unnest the array like this:
    in (select * from unnest(statuses))
    Or this:
    in any (unnest(statuses))

So your query becomes:

select
 *
from (
  select
   model_id,
   model,
   array_agg(id) as jobs,
   array_agg(status) as statuses
  from jobs
  group by model,model_id
) as jobs
where
    'aborted' in any (unnest(statuses))
and
    'pending' not in any (unnest(statuses))
and
    'failed' not in any (unnest(statuses))
and
    'processed' not in any (unnest(statuses));

However, there is a much easier way of writing this.

You can use conditional counts in a having clause:

select
   model_id,
   model,
   array_agg(id) as jobs,
   array_agg(status) as statuses
from jobs
group by model, model_id
having
    count(*) filter (where status = 'aborted') > 0
and
    count(*) filter (where status = 'pending') = 0
and
    count(*) filter (where status = 'failed') = 0
and
    count(*) filter (where status = 'processed') = 0;
3
  • 'aborted' in (select * from unnest(statuses)) can be simplified to 'aborted' = any(statuses)
    – user1822
    Jul 26, 2021 at 15:31
  • @a_horse_with_no_name Didn't know that works in Postgres, you can't do that with SQL Server Jul 26, 2021 at 15:55
  • You can't do unnest() in SQL Server either ;) or use arrays.
    – user1822
    Jul 26, 2021 at 16:00
0

The parentheses around the right-hand-side of the IN argument are mandatory. However, what goes in those parentheses must be a list. An array doesn't work (unless the thing on the left-hand-side is also an array, then you are testing for an array in a list-of-arrays). To test array membership, you can use the 'aborted'=ANY(...) construct instead of IN. But does any of this have to do with json_agg, which you are not using?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.