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 : 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?
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.