Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYE4K-0005XU-VE for pgsql-sql@arkaria.postgresql.org; Wed, 18 Mar 2015 13:31:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YYE4J-0000i5-Pe for pgsql-sql@arkaria.postgresql.org; Wed, 18 Mar 2015 13:31:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YYE4I-0000hz-QN for pgsql-sql@postgresql.org; Wed, 18 Mar 2015 13:31:30 +0000 Received: from mail-am1on0781.outbound.protection.outlook.com ([2a01:111:f400:fe00::781] helo=emea01-am1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYE4B-0001mJ-OS for pgsql-sql@postgresql.org; Wed, 18 Mar 2015 13:31:29 +0000 Received: from AM3PR05MB418.eurprd05.prod.outlook.com (10.242.246.142) by AM3PR05MB420.eurprd05.prod.outlook.com (10.242.246.149) with Microsoft SMTP Server (TLS) id 15.1.112.19; Wed, 18 Mar 2015 13:19:19 +0000 Received: from AM3PR05MB418.eurprd05.prod.outlook.com ([10.242.246.142]) by AM3PR05MB418.eurprd05.prod.outlook.com ([10.242.246.142]) with mapi id 15.01.0112.000; Wed, 18 Mar 2015 13:19:18 +0000 From: Hector Vass To: Andreas Joseph Krogh , "pgsql-sql@postgresql.org" Subject: Re: Effective query for listing flags in use by messages in a folder Thread-Topic: [SQL] Effective query for listing flags in use by messages in a folder Thread-Index: AQHQYIZgPcF8AjH69E2ZKdgW2he4CZ0glMZwgACVnwCAAOfl5YAAEqqAgAACTTmAAAN+gIAAB+y/gAADdgCAAAL+mA== Date: Wed, 18 Mar 2015 13:19:18 +0000 Message-ID: <1426684757283.55625@metametrics.co.uk> References: <1426683287617.19714@metametrics.co.uk>, In-Reply-To: Accept-Language: en-GB, en-US Content-Language: en-GB X-MS-Has-Attach: yes X-MS-TNEF-Correlator: x-originating-ip: [81.149.172.36] authentication-results: visena.com; dkim=none (message not signed) header.d=none; x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:AM3PR05MB420; x-forefront-antispam-report: BMV:1; SFV:NSPM; SFS:(10019020)(92566002)(16236675004)(122556002)(40100003)(87936001)(19627595001)(36756003)(17760045003)(2420400003)(107886001)(77156002)(62966003)(19617315012)(66066001)(2950100001)(46102003)(2501003)(2656002)(117636001)(19580405001)(19627405001)(54356999)(18206015028)(19580395003)(15975445007)(86362001)(106116001)(50986999)(77096005)(74482002)(99936001)(76176999)(102836002)(19625215002); DIR:OUT; SFP:1102; SCL:1; SRVR:AM3PR05MB420; H:AM3PR05MB418.eurprd05.prod.outlook.com; FPR:; SPF:None; MLV:sfv; LANG:en; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:; x-exchange-antispam-report-cfa-test: BCL:0; PCL:0; RULEID:(601004)(5002010)(5005006); SRVR:AM3PR05MB420; BCL:0; PCL:0; RULEID:; SRVR:AM3PR05MB420; x-forefront-prvs: 051900244E Content-Type: multipart/related; boundary="_004_142668475728355625metametricscouk_"; type="multipart/alternative" MIME-Version: 1.0 X-OriginatorOrg: metametrics.co.uk X-MS-Exchange-CrossTenant-originalarrivaltime: 18 Mar 2015 13:19:18.0419 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 0245920b-b0ee-44df-a137-423185636e7a X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM3PR05MB420 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_004_142668475728355625metametricscouk_ Content-Type: multipart/alternative; boundary="_000_142668475728355625metametricscouk_" --_000_142668475728355625metametricscouk_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable My recommendation is to hold the data as key value pairs not as a table wit= h columns for each flag.. Taking your table message ... and loading into this key value pair table me= ssaage2.. drop type if exists myflags cascade; create type myflags as enum('is_seen','is_replied','is_forwarded','is_delet= ed','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_s= een is TRUE; insert into message2 select folder_id,msg,'is_replied' from message where i= s_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 i= s_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 i= s_flagged is TRUE; select is_flag from message2 where folder_id=3D1 group by 1; ? work=3D# select is_flag from message2 where folder_id=3D1 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, Wilt= shire, SN16 0AJ * www.metametrics.co.uk ________________________________ From: pgsql-sql-owner@postgresql.org on be= half of Andreas Joseph Krogh 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=E5 onsdag 18. mars 2015 kl. 13:54:50, skrev Hector Vass >: OK I get it .. drop type if exists myflags cascade; create type myflags as enum('is_seen','is_replied','is_forwarded','is_delet= ed','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 www.visena.com [cid:part_F764646621869HEPVJS] --_000_142668475728355625metametricscouk_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

My recommendation is to h= old 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 wh= ere 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 wh= ere is_deleted is TRUE;
insert into message2 select folder_id,msg,'is_draft' from message wher= e is_draft is TRUE;
insert into message2 select folder_id,msg,'is_flagged' from message wh= ere is_flagged is TRUE;

select is_flag from message2 where folder_id=3D1 group by 1;
​
work=3D# select is_flag from message2 where folder_id=3D1 group by 1;<= /div>
  is_flag
------------
 is_seen
 is_deleted
 is_replied
(3 rows)






Hector Vass




+44(0)7773 352 559

*  Metametrics, International House, 107 Glouc= ester Road,  Malmesbury, Wiltshire, SN16 0AJ

8   www.metam= etrics.co.uk

 

From: pgsql-sql-owner@postg= resql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Josep= h 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 messa= ges in a folder
 
P=E5 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')
;
 
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
=3D""
 
--_000_142668475728355625metametricscouk_-- --_004_142668475728355625metametricscouk_ Content-Type: image/png; name="ATT00001.png" Content-Description: ATT00001.png Content-Disposition: inline; filename="ATT00001.png"; size=1913; creation-date="Wed, 18 Mar 2015 13:01:51 GMT"; modification-date="Wed, 18 Mar 2015 13:01:51 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= --_004_142668475728355625metametricscouk_--