Postgresql - how to use bool_and with group by



  • I have the following schema, that represent collection of messages, where each message can have multiple items (at least 1). Every item belongs to a message. Items can change over time.

    CREATE TABLE messages (
        message_id int8 NOT NULL,
        -- some more fields here...
        CONSTRAINT messages_pk PRIMARY KEY (message_id)
    );
    

    CREATE TABLE items (
    item_id int8 NOT NULL,
    message_id int8 NOT NULL,
    last_update_time timestamptz NOT NULL,
    -- some more fields...
    CONSTRAINT items_pk PRIMARY KEY (item_id)
    );
    ALTER TABLE items ADD CONSTRAINT item_message_fk FOREIGN KEY (message_id) REFERENCES messages(message_id);

    The user can read the items, so I have a table to represent which messages where read and when.

    CREATE TABLE read_items (
        item_id int8 NOT NULL,
        last_read_at timestamptz NOT NULL,
        CONSTRAINT read_items_pk PRIMARY KEY (item_id)
    );
    ALTER TABLE read_items ADD CONSTRAINT read_items_fk FOREIGN KEY (item_id) REFERENCES items(item_id);
    

    Since items can change, if a user has read the item and after that it was changed, I want it to be marked as non-read. So, to see if an item was read or not, I have this query:

    select items.item_id, ...
            case when read_items.last_read_at is null 
                or items.last_update_time > read_items.last_read_at 
            then false else true end
            as is_read
    from items
        left outer join read_items
            on items.item_id = read_items.item_id;
    

    A message is defined read if all its items were read. I'm looking for a way to get the "read status" of all the messages. I thought to use something like the following query, but it fails:

    select 
        messages.message_id, ...
        bool_and(case when read_items.last_read_at is null 
                    or items.last_update_time > read_items.last_read_at 
                then false else true end) 
           as is_read
    from messages 
        join items on messages.message_id = items.message_id 
            left outer join read_items on items.item_id = read_items.item_id 
    group by 
        messages.message_id;
    

    The error message I get is SQL Error [42601]: ERROR: syntax error at or near "bool_and" .

    Is there a way to achieve what I'm trying without a sub query? If not, does using a sub query affects performance?

    Thanks!



  • As @a_horse_with_no_name indicated in their comment, the syntax is correct. The query failed for me due to some issues with the client I used.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2