Re: do I need a table function to do this

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: do I need a table function to do this
Date: 2012-12-30 08:20:42
Message-ID: 50DFF95A.1080907@compulab.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#FFFFFF"
text="#000000">
<div class="moz-cite-prefix">Do you have a unique key on
site,variable? <br>
If not, what do you want in the treatment column if there are rows
for both treatments X and Y&nbsp; or 2 Xs for a specific site and
variable?<br>
<br>
If your data makes sense, you can pivot table t1 and then full
join t2. <br>
<br>
Sim<br>
<br>
<br>
On 12/29/2012 11:45 PM, Kirk Wythers wrote:<br>
</div>
<blockquote cite="mid:99CCEF6F-37D8-4C1A-B4A7-DA8FF19D5048(at)umn(dot)edu"
type="cite">
<pre wrap="">I have been given an interesting problem to solve in a postgres db. I was given two tables

t1:

site treatment variable id (pk)
-------------------------------------------------------------
A X BLUE 1A
B Y RED 2B
A Y GREEN 3A

t2:

rowid (pk) timestamp BLUE RED GREEN
-----------------------------------------------------------------------------------------
1 1332493200 3.4 2.1 5.8
2 1332496800 3.2 2.0 5.8
3 1332500400 3.3 2.2 6.0

I need to combine the site and treatment information from t1 with the variable records in t2. I think I will have to combine these one variable at a time. Something like this (I'm not using the word join, because I don't think this is a join in the regular sense. It's more like some kind of crazy pivot table thing!):

t3:

rowid (pk) timestamp BLUE site treatment
-------------------------------------------------------------------------------------
1 1332493200 3.4 A X
2 1332496800 3.2 A X
3 1332500400 3.3 A X

and then:

t4

rowid (pk) timestamp RED site treatment
-------------------------------------------------------------------------------------
1 1332493200 2.1 B Y
2 1332496800 2.0 B Y
3 1332500400 2.2 B Y

Is this even possible?

</pre>
</blockquote>
<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 2.2 KB

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Amit kapila 2012-12-30 09:15:21 Re: Cursor fetch Problem.
Previous Message Bryan Lee Nuse 2012-12-29 22:18:27 Re: do I need a table function to do this