CODEWITHSUNDEEP
postgresql
Martin
Martin

Reputation: 11336

Convert string to boolean or integer inside bool_and in Postgres

I have the following query to retrieve all the records having state = 'error' until the state is something different based on this question.

The current query looks like this

SELECT * from (select id, subscription_id, state, created_at,
   bool_and(state='error') OVER (order by created_at, id) AS ok 
   FROM messages ORDER by created_at) m2 
   WHERE subscription_id = 1;

However as you can see below all states are being treated as false, ignoring the bool_and(state='error') condition. 

   id    | subscription_id | state |         created_at         | ok 
---------+-----------------+-------+----------------------------+----
   69480 |               1 | error | 2015-06-30 15:20:03.041045 | f
   69482 |               1 | error | 2015-06-30 15:20:04.582907 | f
   69492 |               1 | sent  | 2015-06-30 22:50:04.50478  | f
   69494 |               1 | error | 2015-06-30 22:50:06.067279 | f
   69502 |               1 | error | 2015-07-01 22:50:02.356113 | f

I would expect all ok rows to return t when state = 'error' and f when state has something else.

I read that bool_and only takes booleans and integers, but I'm not sure about this given the result of the condition is supposed to be boolean?

Any idea why this is not working and how to make sure it work?

Upvotes: 1

Views: 740

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51456

you don't partition aggregation for bool_and so it aggregates it over all rows - so you have one state=sent which means bool_and(state='error') is false overall

try adding partition by state to over( part

update better changing

bool_and(state='error') OVER (order by created_at, id) AS ok

to

bool_and(state='error') OVER (partition by state) AS ok

Upvotes: 2

jlee88my
jlee88my

Reputation: 3043

Try:

SELECT  *
FROM    (
        SELECT      id
                , subscription_id
                , state
                , created_at
                , bool_and(state = 'error')
                OVER (PARTITION BY state ORDER BY created_at, id) AS ok 
        FROM        messages
        ORDER BY    created_at
    ) AS m2
WHERE   subscription_id= 1;

Upvotes: 2

Related Questions