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

OK I get it ..

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 message;
create table message(
folder_id integer not NULL,
msg varchar(200),
is_flag myflags
);
insert into message values
(1,'msg b','is_seen'),
(1,'msg c','is_seen'),
(1,'msg d','is_seen'),
(1,'msg d','is_replied'),
(1,'msg e','is_seen'),
(1,'msg e','is_replied'),
(1,'msg h','is_deleted')
;

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

work=# select is_flag from message where folder_id=1 group by 1;
is_flag
------------
is_seen
is_replied
is_deleted
(3 rows)

?

Hector Vass

+44(0)7773 352 559
* Metametrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ
* www.metametrics.co.uk<http://www.metametrics.co.uk/>

________________________________
From: pgsql-sql-owner(at)postgresql(dot)org <pgsql-sql-owner(at)postgresql(dot)org> on behalf of Andreas Joseph Krogh <andreas(at)visena(dot)com>
Sent: 18 March 2015 12:20
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder

På onsdag 18. mars 2015 kl. 13:08:45, skrev Hector Vass <hector(dot)vass(at)metametrics(dot)co(dot)uk<mailto:hector(dot)vass(at)metametrics(dot)co(dot)uk>>:

do you want to post some insert statements to populate the table message with some realistic example data..

Sure:

drop table if EXISTS message;
create table message(
folder_id integer not NULL,
msg varchar NOT NULL,
is_seen boolean NOT NULL default false,
is_replied boolean not null default false,
is_forwarded boolean not null default false,
is_deleted boolean not null default false,
is_draft boolean not null default false,
is_flagged boolean not null default false
);

INSERT INTO message(folder_id, msg, is_seen, is_replied, is_forwarded, is_deleted, is_draft, is_flagged)
values(1, 'msg a', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg b', TRUE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg c', TRUE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg d', TRUE, TRUE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg e', TRUE, TRUE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg f', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg g', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg h', TRUE, FALSE, FALSE, TRUE, FALSE, FALSE)
;

create index message_folder_id_deleted_idx ON message(folder_id) where is_deleted = TRUE;
create index message_folder_id_forwarded_idx ON message(folder_id) where is_forwarded = TRUE;
create index message_folder_id_replied_idx ON message(folder_id) where is_replied = TRUE;
create index message_folder_id_seen_idx ON message(folder_id) where is_seen = TRUE;
create index message_folder_id_flagged_idx ON message(folder_id) where is_flagged = TRUE;
create index message_folder_id_draft_idx ON message(folder_id) where is_draft = TRUE;

select 'is_deleted' as falgs from (select * from message where folder_id = 1 AND is_deleted limit 1) as q
UNION
select 'is_forwarded' from (select * from message where folder_id = 1 AND is_forwarded limit 1) as q
UNION
select 'is_replied' from (select * from message where folder_id = 1 AND is_replied limit 1) as q
UNION
select 'is_seen' from (select * from message where folder_id = 1 AND is_seen limit 1) as q
UNION
select 'is_flagged' from (select * from message where folder_id = 1 AND is_flagged limit 1) as q
UNION
select 'is_draft' from (select * from message where folder_id = 1 AND is_draft limit 1) as q
;

Yields:

falgs
------------
is_deleted
is_replied
is_seen
(3 rows)

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com<mailto:andreas(at)visena(dot)com>
www.visena.com<https://www.visena.com>
[cid:part_F764646561758A3NHED]<https://www.visena.com>