agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Re: Effective query for listing flags in use by messages in a folder
Date: Wed, 18 Mar 2015 13:20:19 +0100 (CET)
Message-ID: <VisenaEmail.32.762467f8760bca83.14c2cd20e8c@tc7-visena> (raw)
In-Reply-To: <1426680525528.15871@metametrics.co.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
På onsdag 18. mars 2015 kl. 13:08:45, skrev Hector Vass <
hector.vass@metametrics.co.uk <mailto:hector.vass@metametrics.co.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_repliedboolean 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 indexmessage_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_idxON message(folder_id) where is_flagged = TRUE;
create indexmessage_folder_id_draft_idx ON message(folder_id) where is_draft =
TRUE; select 'is_deleted' as falgs from (select * from message where folder_id =
1AND is_deleted limit 1) as q UNION select 'is_forwarded' from (select * from
message wherefolder_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
ANDis_seen limit 1) as q UNION select 'is_flagged' from (select * from message
wherefolder_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@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
view thread (12+ messages) latest in thread
Message-ID: <VisenaEmail.32.762467f8760bca83.14c2cd20e8c@tc7-visena>
Permalink: ../VisenaEmail.32.762467f8760bca83.14c2cd20e8c@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.32.762467f8760bca83.14c2cd20e8c@tc7-visena
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: andreas@visena.com
Subject: Re: Effective query for listing flags in use by messages in a folder
In-Reply-To: <VisenaEmail.32.762467f8760bca83.14c2cd20e8c@tc7-visena>
* 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