Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYDVE-0004CZ-OP for pgsql-sql@arkaria.postgresql.org; Wed, 18 Mar 2015 12:55:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YYDVD-0002vf-QE for pgsql-sql@arkaria.postgresql.org; Wed, 18 Mar 2015 12:55:15 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YYDVC-0002tK-1x for pgsql-sql@postgresql.org; Wed, 18 Mar 2015 12:55:14 +0000 Received: from mail-am1on0736.outbound.protection.outlook.com ([2a01:111:f400:fe00::736] helo=emea01-am1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYDV4-0000S0-2K for pgsql-sql@postgresql.org; Wed, 18 Mar 2015 12:55:09 +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 12:54:52 +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 12:54:52 +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/ Date: Wed, 18 Mar 2015 12:54:50 +0000 Message-ID: <1426683287617.19714@metametrics.co.uk> References: <1426680525528.15871@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)(54356999)(19627405001)(18206015028)(2501003)(2656002)(19580405001)(117636001)(102836002)(99936001)(76176999)(19625215002)(15975445007)(19580395003)(74482002)(77096005)(86362001)(50986999)(106116001)(36756003)(2420400003)(17760045003)(92566002)(16236675004)(19627595001)(40100003)(122556002)(87936001)(46102003)(2950100001)(77156002)(107886001)(66066001)(62966003)(19617315012); 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_142668328761719714metametricscouk_"; type="multipart/alternative" MIME-Version: 1.0 X-OriginatorOrg: metametrics.co.uk X-MS-Exchange-CrossTenant-originalarrivaltime: 18 Mar 2015 12:54:50.7721 (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_142668328761719714metametricscouk_ Content-Type: multipart/alternative; boundary="_000_142668328761719714metametricscouk_" --_000_142668328761719714metametricscouk_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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') ; select is_flag from message where folder_id=3D1 group by 1; work=3D# select is_flag from message where folder_id=3D1 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, 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 12:20 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:08:45, skrev Hector Vass >: do you want to post some insert statements to populate the table message wi= th 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_d= eleted, 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_d= eleted =3D TRUE; create index message_folder_id_forwarded_idx ON message(folder_id) where is= _forwarded =3D TRUE; create index message_folder_id_replied_idx ON message(folder_id) where is_r= eplied =3D TRUE; create index message_folder_id_seen_idx ON message(folder_id) where is_seen= =3D TRUE; create index message_folder_id_flagged_idx ON message(folder_id) where is_f= lagged =3D TRUE; create index message_folder_id_draft_idx ON message(folder_id) where is_dra= ft =3D TRUE; select 'is_deleted' as falgs from (select * from message where folder_id = =3D 1 AND is_deleted limit 1) as q UNION select 'is_forwarded' from (select * from message where folder_id =3D 1 AND= is_forwarded limit 1) as q UNION select 'is_replied' from (select * from message where folder_id =3D 1 AND i= s_replied limit 1) as q UNION select 'is_seen' from (select * from message where folder_id =3D 1 AND is_s= een limit 1) as q UNION select 'is_flagged' from (select * from message where folder_id =3D 1 AND i= s_flagged limit 1) as q UNION select 'is_draft' from (select * from message where folder_id =3D 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@visena.com www.visena.com [cid:part_F764646561758A3NHED] --_000_142668328761719714metametricscouk_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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=3D1 group by 1;

work=3D# select is_flag from message where folder_id=3D1 group by 1;
  is_flag
------------
 is_seen
 is_replied
 is_deleted
(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 12:20
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:08:45, skrev Hector Vass <hector.vass@metametrics.co.uk= >:

do you want to post some ins= ert statements to populate the table message with some realistic examp= le data..

 
Sure:
dr=
op table if EXISTS message;=0A=
create table message(=0A=
    folder_id integer not NULL,=0A=
    msg varchar NOT NULL,=
=0A=
    is_seen =
boolean NOT NULL defau=
lt false,=0A=
    is_replied boolean not null de=
fault false,=0A=
    is_forwarded boolean not null =
default false,=0A=
    is_deleted boolean not null de=
fault false,=0A=
    is_draft boolean not null defa=
ult false,=0A=
    is_flagged boolean not null de=
fault false=0A=
);=0A=
=0A=
INSERT INTO message(folder_id, msg, is_seen, is_replied, is_forwarded, is_deleted, is_draft, is_flagged)=0A=
values(1, 'msg a', FALSE, FALSE, FALSE, FALSE, <=
span style=3D"color:rgb(0,0,128); font-weight:bold">FALSE, FALSE)=0A=
    , (1, 'msg b', TRUE, FALSE, FALSE, F=
ALSE, FALSE, FALSE)=0A=
    , (1, 'msg c', TRUE, FALSE, FALSE, F=
ALSE, FALSE, FALSE)=0A=
    , (1, 'msg d', TRUE, TRUE, FALSE, FA=
LSE, FALSE, FALSE)=0A=
    , (1, 'msg e', TRUE, TRUE, FALSE, FA=
LSE, FALSE, FALSE)=0A=
    , (1, 'msg f', FALSE, FALSE, FALSE, =
FALSE, FALSE, FALSE)=0A=
    , (1, 'msg g', FALSE, FALSE, FALSE, =
FALSE, FALSE, FALSE)=0A=
    , (1, 'msg h', TRUE, FALSE, FALSE, T=
RUE, FALSE, FALSE)=0A=
;=0A=
=0A=
create index me=
ssage_folder_id_deleted_idx ON message(folder_id) where is_de=
leted =3D TRUE<=
/span>;=0A=
create index me=
ssage_folder_id_forwarded_idx ON message(folder_id) where is_=
forwarded =3D T=
RUE;=0A=
create index me=
ssage_folder_id_replied_idx ON message(folder_id) where is_re=
plied =3D TRUE<=
/span>;=0A=
create index me=
ssage_folder_id_seen_idx ON message(wh=
ere is_seen =
=3D TRUE=
;=0A=
create index me=
ssage_folder_id_flagged_idx ON message(folder_id) where is_fl=
agged =3D TRUE<=
/span>;=0A=
create index me=
ssage_folder_id_draft_idx ON message(folder_id) w=
here is_draf=
t =3D TRUE;=0A=
=0A=
select 'is_deleted' as falgs from (select * from message where folder_id =3D 1 AND=
 is_deleted =
limit 1) as q=0A=
UNION=0A=
select <=
span style=3D"color:rgb(0,128,0); font-weight:bold">'is_forwarded' <=
span style=3D"color:rgb(0,0,128); font-weight:bold">from (select * from message where folder_id =3D 1 AND is_forwarded limit 1) as q=0A=
UNION=0A=
select <=
span style=3D"color:rgb(0,128,0); font-weight:bold">'is_replied' from (select * from message where folder_id =3D 1 AND is_replied limit 1) as q=0A=
UNION=0A=
select <=
span style=3D"color:rgb(0,128,0); font-weight:bold">'is_seen' from (select * from message where folder_id =3D 1 AND i=
s_seen limit 1) as q=0A=
UNION=0A=
select <=
span style=3D"color:rgb(0,128,0); font-weight:bold">'is_flagged' from (select * from message where folder_id =3D 1 AND is_flagged limit 1) as q=0A=
UNION=0A=
select 'is_draft' from (select =
* from message where folder_id =3D 1 AND is_draft 1) as =
q=0A=
;=0A=
=0A=
Yields:
 
  = falgs
------------
 is_deleted
 is_replied
 is_seen
(3 rows)
 
 
--
Andreas= Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47= 909 56 963
=3D""
 
--_000_142668328761719714metametricscouk_-- --_004_142668328761719714metametricscouk_ 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 12:20:54 GMT"; modification-date="Wed, 18 Mar 2015 12:20:54 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_142668328761719714metametricscouk_--