Re: Effective query for listing flags in use by messages in a folder

My recommendation is to hold the data as key value pairs not as a table with columns for each flag.. 

 

Taking your table message ... and loading into this key value pair table messaage2..

 

drop type if exists myflags cascade;

create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');

drop table if exists message2;

create table message2(

    folder_id integer not NULL,

    msg varchar(200),

    is_flag myflags

);

insert into message2 select folder_id,msg,'is_seen' from message where is_seen is TRUE;

insert into message2 select folder_id,msg,'is_replied' from message where is_replied is TRUE;

insert into message2 select folder_id,msg,'is_forwarded' from message where is_forwarded is TRUE;

insert into message2 select folder_id,msg,'is_deleted' from message where is_deleted is TRUE;

insert into message2 select folder_id,msg,'is_draft' from message where is_draft is TRUE;

insert into message2 select folder_id,msg,'is_flagged' from message where is_flagged is TRUE;

 

select is_flag from message2 where folder_id=1 group by 1;

​

work=# select is_flag from message2 where folder_id=1 group by 1;

  is_flag

------------

 is_seen

 is_deleted

 is_replied

(3 rows)

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com