From jkanter@virginia.edu Tue Nov  4 15:05:50 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 9FB34D1CADD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 Nov 2003 15:05:50 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 11502-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 Nov 2003 11:05:23 -0400 (AST)
Received: from unix.mail.Virginia.EDU (unix.mail.Virginia.EDU [128.143.2.28])
	by svr1.postgresql.org (Postfix) with ESMTP id C6489D1B4EF
	for <pgsql-sql@postgresql.org>; Tue,  4 Nov 2003 11:05:09 -0400 (AST)
Received: from smtp.mail.Virginia.EDU (smtp.mail.Virginia.EDU [128.143.22.52])
	by unix.mail.Virginia.EDU (Postfix) with ESMTP id E9284AADD
	for <pgsql-sql@postgresql.org>; Tue,  4 Nov 2003 10:04:58 -0500 (EST)
Received: from virginia.edu (d-137-133-128.bootp.Virginia.EDU
 [137.54.133.128])
	by smtp.mail.Virginia.EDU (Postfix) with ESMTP id 970472102C
	for <pgsql-sql@postgresql.org>; Tue,  4 Nov 2003 10:04:58 -0500 (EST)
Message-ID: <3FA7C2CE.4000902@virginia.edu>
Date: Tue, 04 Nov 2003 10:16:30 -0500
From: Jodi Kanter <jkanter@virginia.edu>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.5) Gecko/20031007
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Postgres SQL List <pgsql-sql@postgresql.org>
Subject: query assistance
Content-Type: multipart/alternative;
	boundary="------------040008040309000208080508"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/14
X-Sequence-Number: 15829

This is a multi-part message in MIME format.
--------------040008040309000208080508
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Is there a straight forward way to pull out duplicates in a particular 
field given a value in another field?
For example, I have a table that lists users and study names associated 
with those users. Each user can have one or more study names. My goal is 
to determine if any of these people have duplicate study names. There 
are duplicated study names across the system and that is ok. I just want 
to see if any users have duplicate study names among their studies.
My table looks like this:

                                   Table "public.study"
    Column    |            Type             |                
Modifiers                
--------------+-----------------------------+------------------------------------------
 sty_pk       | integer                     | not null default 
nextval('pk_seq'::text)
 study_name   | character varying(128)      | not null
 start_date   | timestamp without time zone |
 sty_comments | text                        |
 created_by   | integer                     |
Indexes: study_pkey primary key btree (sty_pk)

I am concerened with study_name. The created_by field tells me who owns it.
Thanks
Jodi

-- 

/_______________________________
//Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu <mailto:jkanter@virginia.edu>/

 

/ /

/ /


--------------040008040309000208080508
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Is there a straight forward way to pull out duplicates in a particular
field given a value in another field?<br>
For example, I have a table that lists users and study names associated
with those users. Each user can have one or more study names. My goal
is to determine if any of these people have duplicate study names.
There are duplicated study names across the system and that is ok. I
just want to see if any users have duplicate study names among their
studies.<br>
My table looks like this:<br>
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table "public.study"<br>
&nbsp;&nbsp;&nbsp; Column&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Modifiers&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
--------------+-----------------------------+------------------------------------------<br>
&nbsp;sty_pk&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | integer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null default
nextval('pk_seq'::text)<br>
&nbsp;study_name&nbsp;&nbsp; | character varying(128)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null<br>
&nbsp;start_date&nbsp;&nbsp; | timestamp without time zone | <br>
&nbsp;sty_comments | text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | <br>
&nbsp;created_by&nbsp;&nbsp; | integer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | <br>
Indexes: study_pkey primary key btree (sty_pk)<br>
<br>
I am concerened with study_name. The created_by field tells me who owns
it.<br>
Thanks<br>
Jodi<br>
<br>
<div class="moz-signature">-- <br>
<meta content="text/html; " http-equiv="Content-Type">
<meta content="Word.Document" name="ProgId">
<meta content="Microsoft Word 9" name="Generator">
<meta content="Microsoft Word 9" name="Originator">
<link href="./email%20sig_files/filelist.xml" rel="File-List">
<title>________________________________</title>
<!--[if gte mso 9]><xml>
 <o:DocumentProperties>
  <o:LastAuthor>jlk3x</o:LastAuthor>
  <o:Revision>2</o:Revision>
  <o:TotalTime>6</o:TotalTime>
  <o:Created>2002-01-15T15:50:00Z</o:Created>
  <o:LastSaved>2002-01-15T15:50:00Z</o:LastSaved>
  <o:Pages>1</o:Pages>
  <o:Words>27</o:Words>
  <o:Characters>157</o:Characters>
  <o:Company>University of Virginia</o:Company>
  <o:Lines>1</o:Lines>
  <o:Paragraphs>1</o:Paragraphs>
  <o:CharactersWithSpaces>192</o:CharactersWithSpaces>
  <o:Version>9.2720</o:Version>
 </o:DocumentProperties>
</xml><![endif]-->
<style>
<!--
 /* Font Definitions */
@font-face
	{font-family:"MS Mincho";
	panose-1:0 0 0 0 0 0 0 0 0 0;
	mso-font-alt:"\FF2D\FF33 \660E\671D";
	mso-font-charset:128;
	mso-generic-font-family:roman;
	mso-font-format:other;
	mso-font-pitch:fixed;
	mso-font-signature:1 134676480 16 0 131072 0;}
@font-face
	{font-family:"\@MS Mincho";
	panose-1:0 0 0 0 0 0 0 0 0 0;
	mso-font-charset:128;
	mso-generic-font-family:roman;
	mso-font-format:other;
	mso-font-pitch:fixed;
	mso-font-signature:1 134676480 16 0 131072 0;}
 /* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{mso-style-parent:"";
	margin:0in;
	margin-bottom:.0001pt;
	mso-pagination:widow-orphan;
	font-size:12.0pt;
	font-family:"Times New Roman";
	mso-fareast-font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;
	text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;
	text-underline:single;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
	{margin:0in;
	margin-bottom:.0001pt;
	mso-pagination:widow-orphan;
	font-size:10.0pt;
	font-family:"Courier New";
	mso-fareast-font-family:"Times New Roman";}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 65.95pt 1.0in 65.95pt;
	mso-header-margin:.5in;
	mso-footer-margin:.5in;
	mso-paper-source:0;}
div.Section1
	{page:Section1;}
-->
</style>
<div class="Section1">
<p class="MsoNormal"><i><span
 style="font-size: 9pt; font-family: Arial;">_______________________________<br>
</span></i><i><span style="font-size: 10pt;">Jodi L Kanter<br>
BioInformatics Database Administrator<br>
University of Virginia<br>
(434) 924-2846<br>
<a href="mailto:jkanter@virginia.edu">jkanter@virginia.edu</a></span></i><span
 style="font-size: 11pt; font-family: Arial;"><br style="">
<!--[if !supportLineBreakNewLine]--><br style="">
<!--[endif]--><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size: 11pt; font-family: Arial;"><!--[if !supportEmptyParas]-->&nbsp;<!--[endif]--><o:p></o:p></span></p>
<p class="MsoNormal"><i><span
 style="font-size: 9pt; font-family: Arial;"><!--[if !supportEmptyParas]-->&nbsp;<!--[endif]--><o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span
 style="font-size: 9pt; font-family: Arial;"><!--[if !supportEmptyParas]-->&nbsp;<!--[endif]--><o:p></o:p></span></i></p>
</div>
</div>
</body>
</html>

--------------040008040309000208080508--




From dev@archonet.com Tue Nov  4 15:48:08 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 6EEE3D1B561
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 Nov 2003 15:48:08 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 21118-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  4 Nov 2003 11:47:40 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [200.46.204.70])
	by svr1.postgresql.org (Postfix) with ESMTP id E00E5D1CA66
	for <pgsql-sql@postgresql.org>; Tue,  4 Nov 2003 11:47:36 -0400 (AST)
Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net
	[194.217.242.88])
	by svr4.postgresql.org (Postfix) with ESMTP id 979E61CB4654
	for <pgsql-sql@postgresql.org>; Tue,  4 Nov 2003 15:47:20 +0000 (GMT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-30.mail.demon.net with esmtp (Exim 3.35 #1)
	id 1AH3LI-000Krj-0U; Tue, 04 Nov 2003 15:43:40 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 25E91170FA; Tue,  4 Nov 2003 15:43:38 +0000 (GMT)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id B277216EEB; Tue,  4 Nov 2003 15:43:37 +0000 (GMT)
From: Richard Huxton <dev@archonet.com>
To: Jodi Kanter <jkanter@virginia.edu>,
	Postgres SQL List <pgsql-sql@postgresql.org>
Subject: Re: query assistance
Date: Tue, 4 Nov 2003 15:43:35 +0000
User-Agent: KMail/1.5
References: <3FA7C2CE.4000902@virginia.edu>
In-Reply-To: <3FA7C2CE.4000902@virginia.edu>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200311041543.35899.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/16
X-Sequence-Number: 15831

On Tuesday 04 November 2003 15:16, Jodi Kanter wrote:
> Is there a straight forward way to pull out duplicates in a particular
> field given a value in another field?
> For example, I have a table that lists users and study names associated
> with those users. Each user can have one or more study names. My goal is
> to determine if any of these people have duplicate study names. There
> are duplicated study names across the system and that is ok. I just want
> to see if any users have duplicate study names among their studies.

Something like:

SELECT
  created_by, study_name, count(sty_pk)
FROM
  study
GROUP BY
  created_by, study_name
HAVING
  count(sty_pk) > 1;

-- 
  Richard Huxton
  Archonet Ltd



From grzm@myrealbox.com Wed Nov  5 04:10:27 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 371C3D1BB5F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  5 Nov 2003 04:10:27 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 50937-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  5 Nov 2003 00:09:56 -0400 (AST)
Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com
	[192.108.102.143])
	by svr1.postgresql.org (Postfix) with ESMTP id EDB58D1B50C
	for <pgsql-sql@postgresql.org>; Wed,  5 Nov 2003 00:09:55 -0400 (AST)
Received: from myrealbox.com glaesema@smtp-send.myrealbox.com [61.115.206.98]
	by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.44 $
	on Novell NetWare; Tue, 04 Nov 2003 21:09:48 -0700
Date: Wed, 5 Nov 2003 13:09:57 +0900
Subject: Re: query assistance
Content-Type: multipart/alternative; boundary=Apple-Mail-19--496115936
Mime-Version: 1.0 (Apple Message framework v552)
From: Michael Glaesemann <grzm@myrealbox.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <3FA7C2CE.4000902@virginia.edu>
Message-Id: <EAB5A534-0F45-11D8-B410-0005029FC1A7@myrealbox.com>
X-Mailer: Apple Mail (2.552)
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/25
X-Sequence-Number: 15840

--Apple-Mail-19--496115936
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	delsp=yes;
	charset=ISO-8859-1;
	format=flowed

Hi Jodi,

On Wednesday, November 5, 2003, at 12:16 AM, Jodi Kanter wrote:

> Is there a straight forward way to pull out duplicates in a particular=20=
=20
> field given a value in another field?
> For example, I have a table that lists users and study names=20=20
> associated with those users. Each user can have one or more study=20=20
> names.

If I understand you correctly, this is the table you're interested in.=20=
=20
Your public.study table doesn't include any users as far as I can tell=20=
=20
(though please correct me if I'm misunderstanding you).

> =A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0 Table "public.study"
> =A0=A0=A0 Column=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=20=20
> Modifiers=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
> --------------+-----------------------------=20
> +------------------------------------------
> =A0sty_pk=A0=A0=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0 | not null default=20=20
> nextval('pk_seq'::text)
> =A0study_name=A0=A0 | character varying(128)=A0=A0=A0=A0=A0 | not null
> =A0start_date=A0=A0 | timestamp without time zone |
> =A0sty_comments | text=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 |
> =A0created_by=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 |
> Indexes: study_pkey primary key btree (sty_pk)

I think something like this is what you're looking for:

SELECT user, study_name, COUNT(*)
FROM <table linking user and study_name>
GROUP BY user, study_name
HAVING COUNT(*) > 1;

where the FROM clause lists the table linking users and study_names.

Does this help?

Michael
grzm myrealbox com

--Apple-Mail-19--496115936
Content-Transfer-Encoding: quoted-printable
Content-Type: text/enriched;
	charset=ISO-8859-1

Hi Jodi,


On Wednesday, November 5, 2003, at 12:16 AM, Jodi Kanter wrote:


<excerpt><color><param>0000,0000,0000</param>Is there a straight
forward way to pull out duplicates in a particular field given a value
in another field?

For example, I have a table that lists users and study names
associated with those users. Each user can have one or more study
names.

</color></excerpt>

If I understand you correctly, this is the table you're interested in.
Your public.study table doesn't include any users as far as I can tell
(though please correct me if I'm misunderstanding you).


<excerpt><color><param>0000,0000,0000</param>=A0=A0=A0
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0 Table "public.study"

=A0=A0=A0 Column=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
Modifiers=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0

--------------+-----------------------------+------------------------------=
------------

=A0sty_pk=A0=A0=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0 | not null default
nextval('pk_seq'::text)

=A0study_name=A0=A0 | character varying(128)=A0=A0=A0=A0=A0 | not null

=A0start_date=A0=A0 | timestamp without time zone |

=A0sty_comments | text=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 |

=A0created_by=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0 |

Indexes: study_pkey primary key btree (sty_pk)

</color></excerpt><color><param>0000,0000,0000</param>

I think something like this is what you're looking for:


SELECT user, study_name, COUNT(*)

FROM <<table linking user and study_name>

GROUP BY user, study_name

HAVING COUNT(*) > 1;


</color>where the FROM clause lists the table linking users and
study_names.


Does this help?


Michael

grzm myrealbox com


--Apple-Mail-19--496115936--




From gwchamb@gmail.com Wed Dec  12 16:58:59 2007
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 115742E3D1D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 12:58:59 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 75201-01-3 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 12:58:54 -0400 (AST)
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.190])
	by postgresql.org (Postfix) with ESMTP id A2F952E0099
	for <pgsql-sql@postgresql.org>; Wed, 12 Dec 2007 12:58:22 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id f5so240270nfh.23
	for <pgsql-sql@postgresql.org>; Wed, 12 Dec 2007 08:58:21 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	bh=ytkcQXZuO3lBrEmod0wbBlV8sjRCEOdWSmdVsMIeWsE=;
	b=u6/Sn0h8TuKuFgPE0vRKyNKPQV4IBxGCsvajk2rfebk+7kT8iItbU12GXUImSFTjCW5udmHU0sdkmBViUP6XbXPpGsCMVhmrOmS+0xJYNwNapuw3Z7PnbNaB8cwa/JiqATSRJ0YryUPpEGvMB2uTd5sFfFVBIfAbsdKupGYIA6g=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=nPBFcBMtGLzJFZws5/QUMxpY3lvDiESaHLzEyPEfJUOl+7+vc1qbtvGzW0NZJjKPYXkYV7S0fCrx2sT0YfRob6aVsbEdYA92OwmrsnvxxOzfHvLOOk9Ay49v8349CeFjCubKdsjPwRR4Cz+e6LAFHYk0rqcGRsrDMuyzEOcB/OE=
Received: by 10.86.100.7 with SMTP id x7mr829967fgb.42.1197478700834;
	Wed, 12 Dec 2007 08:58:20 -0800 (PST)
Received: by 10.86.51.13 with HTTP; Wed, 12 Dec 2007 08:58:20 -0800 (PST)
Message-ID: <302670f20712120858k210dd812l8b232ce98d5d162f@mail.gmail.com>
Date: Wed, 12 Dec 2007 11:58:20 -0500
From: "Gary Chambers" <gwchamb@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Query Assistance
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/70
X-Sequence-Number: 29762

All...

I have a simple table in PostgreSQL 8.2.5:

CREATE TABLE power_transitions (
    -- Transition ID (PK)
    tid integer NOT NULL,
    -- State ID (0 = Unknown, 1 = Online (AC power), 2 = Offline (Battery)
    sid smallint NOT NULL,
    -- Timestamp of transition
    statetime timestamp without time zone DEFAULT now() NOT NULL,
    -- Is this a real outage?
    is_outage boolean DEFAULT true NOT NULL
);

It contains a log of power outages (transitions).  I'd like to create
query that returns a transition offline time and associated return to
online time.  Is there a better way of handling this?  I am open to
schema change suggestions.  Thanks very much!

-- Gary Chambers

// Nothing fancy and nothing Microsoft!



From darcy@druid.net Wed Dec  12 17:31:40 2007
Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 2B7F52E0098
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 13:31:40 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 03224-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 13:31:31 -0400 (AST)
Received: from smaug.vex.net (smaug.vex.net [208.76.104.132])
	by postgresql.org (Postfix) with ESMTP id 9BD562E0099
	for <pgsql-sql@postgresql.org>; Wed, 12 Dec 2007 13:31:32 -0400 (AST)
Received: from dilbert (dilbert.druid.net [207.35.13.14])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by smaug.vex.net (Postfix) with ESMTP id C0F2717194;
	Wed, 12 Dec 2007 12:31:29 -0500 (EST)
Date: Wed, 12 Dec 2007 12:31:29 -0500
From: "D'Arcy J.M. Cain" <darcy@druid.net>
To: "Gary Chambers" <gwchamb@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query Assistance
Message-Id: <20071212123129.ad22c7ce.darcy@druid.net>
In-Reply-To: <302670f20712120858k210dd812l8b232ce98d5d162f@mail.gmail.com>
References: <302670f20712120858k210dd812l8b232ce98d5d162f@mail.gmail.com>
X-Mailer: Sylpheed 2.4.5 (GTK+ 2.12.0; i386--netbsdelf)
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/71
X-Sequence-Number: 29763

On Wed, 12 Dec 2007 11:58:20 -0500
"Gary Chambers" <gwchamb@gmail.com> wrote:
> All...
> 
> I have a simple table in PostgreSQL 8.2.5:
> 
> CREATE TABLE power_transitions (
>     -- Transition ID (PK)
>     tid integer NOT NULL,
>     -- State ID (0 = Unknown, 1 = Online (AC power), 2 = Offline (Battery)
>     sid smallint NOT NULL,
>     -- Timestamp of transition
>     statetime timestamp without time zone DEFAULT now() NOT NULL,
>     -- Is this a real outage?
>     is_outage boolean DEFAULT true NOT NULL
> );
> 
> It contains a log of power outages (transitions).  I'd like to create
> query that returns a transition offline time and associated return to
> online time.  Is there a better way of handling this?  I am open to
> schema change suggestions.  Thanks very much!

Have you considered this?

CREATE TABLE power_transitions (
    -- Transition ID (PK)
    tid integer NOT NULL,
    -- Timestamp of power off (
    starttime timestamp without time zone DEFAULT now() NOT NULL,
    -- Timestamp of power on (
    endtime timestamp without time zone,
    -- Is this a real outage?  may not be needed.
    is_outage boolean DEFAULT true NOT NULL
);

The is_outage bool could be handled with special timestamps (e.g.
EPOCH) but I am not sure what it signifies to you.  Of course you have
to deal with false transitions but I don't know what your information
capture system is so I can't work out all the details but this seems
like a good base for what you want. Every row that has a valid start
and end time is a complete record of an outage.

I am also thinking of a scheme that uses two tables but I don't really
know your environment or requirements.  I am assuming that you spend
more time querying the table than updating it.  If not your problem
isn't your database, it's your power plant.  :-)

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.



From gwchamb@gmail.com Wed Dec  12 18:00:57 2007
Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 326732E2E62
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 14:00:57 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 10991-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 14:00:51 -0400 (AST)
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172])
	by postgresql.org (Postfix) with ESMTP id EAE252E015D
	for <pgsql-sql@postgresql.org>; Wed, 12 Dec 2007 14:00:51 -0400 (AST)
Received: by ug-out-1314.google.com with SMTP id j3so660425ugf.49
	for <pgsql-sql@postgresql.org>; Wed, 12 Dec 2007 10:00:50 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	bh=/aXLOwCVgFk6rHwSV0s/lgpsFTLY2b82FNzNv++i7ko=;
	b=eWrMFnUVsk/62l6VpIKYcJ/9ekSTk8o5kaX0Ro5cCQOOrR/ZwGVKBdcIwS/D4Ss1XkhFrEpT38ob4Yf+pVqn1OOst/CEzYXJl2+BvFcYbwG/exW1BeMiW1vKC/Z/vF+pMf6TKAKZX53HiqFI5il7krufMfahp/ikNOz8xvjbTbg=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=HCF6XtvKtgJqe+wHvoBdYQuww0Jqnxkjdn8xDL4oeyadTlmmfN3tuOW7qg4wzXoMCkfCwp3Bqv3OXT61NpMnBmYdwMZcnpJEH7mJOVGlmWdH9dyWWnk5NaLeH58xdInL5k46L2KxO23+YcmN2qNTIVBHkVC9mZFt/Oxco14iwls=
Received: by 10.86.82.16 with SMTP id f16mr882368fgb.60.1197482450693;
	Wed, 12 Dec 2007 10:00:50 -0800 (PST)
Received: by 10.86.51.13 with HTTP; Wed, 12 Dec 2007 10:00:50 -0800 (PST)
Message-ID: <302670f20712121000w6d3c1ecg39620cfa8c39c606@mail.gmail.com>
Date: Wed, 12 Dec 2007 13:00:50 -0500
From: "Gary Chambers" <gwchamb@gmail.com>
Subject: Re: Query Assistance
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20071212123129.ad22c7ce.darcy@druid.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <302670f20712120858k210dd812l8b232ce98d5d162f@mail.gmail.com>
	<20071212123129.ad22c7ce.darcy@druid.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.182 tagged_above=0 required=5 tests=BAYES_05=-1.11,
	MISSING_HEADERS=1.292
X-Spam-Level: 
X-Archive-Number: 200712/72
X-Sequence-Number: 29764

D'Arcy...

> Have you considered this?

I considered integrating the begin and end times into the table.  I'm
capturing the data via ACPI events, so it's "transactional" by nature.
I want to be able to keep track of false transitions (hence the
is_outage field).

I'm looking for a way to simplify the transitions output on my web
browser, and I want to combine an offline and a corresponding online
time into a single line.

> I am also thinking of a scheme that uses two tables but I don't really
> know your environment or requirements.  I am assuming that you spend
> more time querying the table than updating it.  If not your problem
> isn't your database, it's your power plant.  :-)

It's nothing complex at all.  The power in my neighborhood is
infamously unstable, and I'm merely keep records to complain as much
as I can to the power company.

Thanks for taking the time to reply!

-- Gary Chambers

// Nothing fancy and nothing Microsoft!



From dev@archonet.com Wed Dec  12 20:21:28 2007
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 16AC92E2D75
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 16:21:28 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 46605-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed, 12 Dec 2007 16:21:12 -0400 (AST)
Received: from fhw-relay07.plus.net (fhw-relay07.plus.net [212.159.14.215])
	by postgresql.org (Postfix) with ESMTP id 811612E35EE
	for <pgsql-sql@postgresql.org>; Wed, 12 Dec 2007 16:21:24 -0400 (AST)
Received: from [84.51.143.99] (helo=server3.office.archonet.com)
	by fhw-relay07.plus.net with esmtp (Exim) id 1J2Y54-0006wI-Ec;
	Wed, 12 Dec 2007 20:21:22 +0000
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 2304527404B;
	Wed, 12 Dec 2007 20:21:22 +0000 (GMT)
Message-ID: <476042C1.8070707@archonet.com>
Date: Wed, 12 Dec 2007 20:21:21 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: Gary Chambers <gwchamb@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Query Assistance
References: <302670f20712120858k210dd812l8b232ce98d5d162f@mail.gmail.com>	
	<20071212123129.ad22c7ce.darcy@druid.net>
	<302670f20712121000w6d3c1ecg39620cfa8c39c606@mail.gmail.com>
In-Reply-To: <302670f20712121000w6d3c1ecg39620cfa8c39c606@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/74
X-Sequence-Number: 29766

Gary Chambers wrote:
> D'Arcy...
> 
>> Have you considered this?
> 
> I considered integrating the begin and end times into the table.  I'm
> capturing the data via ACPI events, so it's "transactional" by nature.
> I want to be able to keep track of false transitions (hence the
> is_outage field).
> 
> I'm looking for a way to simplify the transitions output on my web
> browser, and I want to combine an offline and a corresponding online
> time into a single line.

How about a "paired_with" field that references the power_transitions 
table and a trigger. When you insert a new row, it checks what the 
previous row was - if it's a down & this is an up, then set the 
paired_with field on each.

-- 
   Richard Huxton
   Archonet Ltd