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

Поиск

Список

Период

Сортировка

От	Hector Vass
Тема	Re: Effective query for listing flags in use by messages in a folder
Дата	18 марта 2015 г. 16:31:31
Msg-id	1426684757283.55625@metametrics.co.uk обсуждение исходный текст
Ответ на	Re: Effective query for listing flags in use by messages in a folder (Andreas Joseph Krogh <andreas@visena.com>)
Ответы	Re: Effective query for listing flags in use by messages in a folder (Andreas Joseph Krogh <andreas@visena.com>) Re: Effective query for listing flags in use by messages in a folder (Hector Vass <hector.vass@metametrics.co.uk>)
Список	pgsql-sql

Дерево обсуждения

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)

Hector Vass

+44(0)7773 352 559

* Metametrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ

8 www.metametrics.co.uk

From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Joseph Krogh <andreas@visena.com>
Sent: 18 March 2015 13:01
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder

På onsdag 18. mars 2015 kl. 13:54:50, skrev Hector Vass <hector.vass@metametrics.co.uk>:

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')
;