Convert string to boolean or integer inside bool_and in Postgres

Question

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?

you dont 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 - right?.. try adding partition by state to over( — Vao Tsun
– Vao Tsun, Commented Jun 13, 2017 at 10:33

Vao Tsun · Accepted Answer · 2017-06-13 10:59:46Z

2

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

edited Jun 13, 2017 at 10:59

answered Jun 13, 2017 at 10:40

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

3 Comments

Martin Over a year ago

However when adding at the end of the query AND ok = true it seems it is not stopping after finding the state = sent but rather just retrieving all the 4 state = 'error' records.

Vao Tsun Over a year ago

can you please update post with exact expected return, data sample and table structure? So I could debug the problem?..

Martin Over a year ago

I believe this is better formulated as a new question, please see here: stackoverflow.com/questions/44519961/…

jlee88my · Accepted Answer · 2017-06-13 10:46:35Z

2

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;

answered Jun 13, 2017 at 10:46

jlee88my

3,0531 gold badge23 silver badges28 bronze badges

Collectives™ on Stack Overflow

Convert string to boolean or integer inside bool_and in Postgres

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related