agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Hector Vass <hector.vass@metametrics.co.uk>
To: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Effective query for listing flags in use by messages in a folder
Date: Wed, 18 Mar 2015 13:19:18 +0000
Message-ID: <1426684757283.55625@metametrics.co.uk> (raw)
In-Reply-To: <VisenaEmail.35.3a752eb0ccc59659.14c2cf6ffdf@tc7-visena>
References: <1426683287617.19714@metametrics.co.uk>
	<VisenaEmail.35.3a752eb0ccc59659.14c2cf6ffdf@tc7-visena>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
*   www.metametrics.co.uk<http://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<mailto: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')
;

Not quite; you have 2 entries, one for each flag, for "msg d". I must have one tuple per message in this table.

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com<mailto:andreas@visena.com>
www.visena.com<https://www.visena.com;
[cid:part_F764646621869HEPVJS]<https://www.visena.com;

Attachments:

  [image/png] ATT00001.png (1.9K, ../1426684757283.55625@metametrics.co.uk/3-ATT00001.png)
  download | view image

view thread (12+ messages)  latest in thread

Message-ID: <1426684757283.55625@metametrics.co.uk>
Permalink:  ../1426684757283.55625@metametrics.co.uk/
Also on:    postgresql.org/message-id/1426684757283.55625@metametrics.co.uk

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: hector.vass@metametrics.co.uk, andreas@visena.com
  Subject: Re: Effective query for listing flags in use by messages in a folder
  In-Reply-To: <1426684757283.55625@metametrics.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox