Re: [SQL] JOIN
Any help, please?
On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote:
Hi everybody,
I have the following table:
count | theme | receiver| date
| dates
|
---+---+--++-+---
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
I want to add up the count column grouped by theme and receiver for the
dates included in the dates column.
So I have the following query:
SELECT SUM(A.count),
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
The result of the query is:
sum | theme |receiver|
dates
---+---+--+
3 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
3 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
18 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
4 | LIA | +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
6 | LIA | +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
10 | LIA | +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
The result is wrong. I don't know what it is wrong at my query.
Please help.
Best,
Loredana
Re: [SQL] JOIN
Loredana Curugiu wrote: Any help, please? So I have the following query: SELECT SUM(A.count), A.theme, A.receiver, A.dates FROM my_table A INNER JOIN my_table B ON A.theme=B.theme AND A.receiver=B.receiver AND A.date=ANY(B.dates) GROUP BY A.theme,A.receiver, A.dates; [snip] The result is wrong. I don't know what it is wrong at my query. Please help. You don't actually say what's wrong. What are you expecting as output? Oh, and your "date" column isn't - it's a timestamp with time-zone. That might or might not cause confusion with daylight-saving-times. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] JOIN
So I have the following query:>> >>SELECT SUM(A.count), >> A.theme, >> A.receiver, >> A.dates >> FROM my_table A >> INNER JOIN my_table B >> ON A.theme=B.theme >> AND A.receiver=B.receiver >> AND A.date=ANY(B.dates) >> GROUP BY A.theme,A.receiver, A.dates; [snip] >> >> The result is wrong. I don't know what it is wrong at my query. >> Please help. You don't actually say what's wrong. What are you expecting as output? I am trying to say that sum column it is not calculated correctly. Oh, and your "date" column isn't - it's a timestamp with time-zone. That might or might not cause confusion with daylight-saving-times. Yes, I am working with timestamp with time zone . Regards, Loredana
Re: [SQL] JOIN
Loredana Curugiu wrote: So I have the following query:>> >>SELECT SUM(A.count), >> A.theme, >> A.receiver, >> A.dates >> FROM my_table A >> INNER JOIN my_table B >> ON A.theme=B.theme >> AND A.receiver=B.receiver >> AND A.date=ANY(B.dates) >> GROUP BY A.theme,A.receiver, A.dates; [snip] >> >> The result is wrong. I don't know what it is wrong at my query. >> Please help. You don't actually say what's wrong. What are you expecting as output? I am trying to say that sum column it is not calculated correctly. But you're not saying what you think the figure(s) should be. There's two possibilities: 1. SUM() has a bug and can't add up numbers. Unlikely 2. The INNER JOIN isn't working as you think it should. This seems more likely. Try the query without totalling, just to see what matches you're getting. Oh, and your "date" column isn't - it's a timestamp with time-zone. That might or might not cause confusion with daylight-saving-times. Yes, I am working with timestamp with time zone . You're happy that daylight-saving adjustments aren't causing any problems with your timezone settings then? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] JOIN
"Loredana Curugiu" <[EMAIL PROTECTED]> writes: > I am trying to say that sum column it is not calculated correctly. It looks like your counts are high. That would imply that your join clauses are matching more than one combination of rows. Ie, it's counting some rows multiple times because there are multiple join matches. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] JOIN
Hi everybody,
I have the following table:
count | theme | receiver| date
| dates
|
---+---+--++-+---
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
I want to add up the count column grouped by theme and receiver for the
dates included in the dates column.
So I have the following query:
SELECT SUM(A.count),
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
The result of the query is:
sum | theme |receiver|
dates
---+---+--+
3 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
3 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
18 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
4 | LIA | +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
6 | LIA | +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
10 | LIA | +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
The result is wrong. I don't know what it is wrong at my query.
Please help.
Best,
Loredana
Re: [SQL] JOIN
Hey, Loredana.
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that appears
on count for every record whose date is in dates column, is this correct?
But in what record's dates column? On all of them? Or just the dates column
of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output for
ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Thanx in advance
Best,
Oliveiros
2007/6/5, Loredana Curugiu <[EMAIL PROTECTED]>:
Any help, please?
On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote:
>
> Hi everybody,
>
> I have the following table:
>
> count | theme | receiver| date
> | dates
>|
>
---+---+--++-+---
>
> 2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |
> 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |
> 3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>|
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
> |
> 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |
> 1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> |
>
> I want to add up the count column grouped by theme and receiver for the
> dates included in the dates column.
> So I have the following query:
>
>SELECT SUM(A.count),
> A.theme,
> A.receiver,
> A.dates
> FROM my_table A
> INNER JOIN my_table B
> ON A.theme=B.theme
> AND A.receiver=B.receiver
> AND A.date=ANY(B.dates)
> GROUP BY A.theme,A.receiver, A.dates;
>
> The result of the query is:
>
> sum | theme |receiver|
> dates
>
>
---+---+--+
> 3 | CRIS | +40741775622 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> 2 | CRIS | +40741775622 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> 3 | CRIS | +40741775622 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>18 | CRIS | +40741775622 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> 4 | LIA | +40741775621 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
> 4 | LIA | +40741775621 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> 6 | LIA | +40741775621 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>10 | LIA | +40741775621 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>
> The result is wrong. I don't know what it is wrong at my query.
> Please help.
>
>
> Best,
> Loredana
>
>
>
>
--
O Quê? SQL Server 2005 Express Edition? for
free? easy-to-use?? lightweight??? and embeddable??? Isso deve ser uma
fortuna, homem!
Re: [SQL] JOIN
You don't actually say what's wrong. What are you expecting as output?
I should obtain the following result:
sum | theme | receiver |
dates
+--+--+
8 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
3 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA | +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA | +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA | +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
2 | LIA | +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)
Try the query without totalling, just to see what matches you're getting.
I obtain the same result without sum column.
You're happy that daylight-saving adjustments aren't causing any
problems with your timezone settings then?
I took in consideration the daylight-saving adjustments.
Regards,
Loredana
[SQL] CREATE RULE with WHERE clause
Hello, I need to create a rule, but I need that it have a WHERE clause, how bellow: CREATE RULE rule_role_sul AS ON SELECT TO t1 WHERE roles = 'role_sul' DO INSTEAD SELECT field1, field2 FROM t2; CREATE RULE rule_role_sul AS ON SELECT TO t1 WHERE roles = 'role_norte' DO INSTEAD SELECT field3, field4 FROM t2; Someone knows how can I do it? I appreciate any help Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] JOIN
Loredana Curugiu wrote:
You don't actually say what's wrong. What are you expecting as output?
I should obtain the following result:
[snip]
Well, I've attached a test script using your example data and a copy of
my results. Nothing leaping out as wrong here. It's entirely possible
I've not had enough coffee today though and I'm missing something
staring me in the face...
--
Richard Huxton
Archonet Ltd
sum | theme | receiver | date |
dates
-+---+--++
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
3 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
18 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
4 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
6 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
10 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)
count | theme | receiver | date |
dates
---+---+--++
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06
Re: [SQL] [NOVICE] JOIN
Loredana Curugiu wrote:
> Hi everybody,
>
> I have the following table:
>
> count | theme | receiver| date
> | dates
>|
> ---+---+--++-+---
>
> 2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |
> 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>
> |
> 3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>|
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
>
> |
> 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>
> |
> 1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> |
>
> I want to add up the count column grouped by theme and receiver for the
> dates included in the dates column.
> So I have the following query:
>
>SELECT SUM(A.count),
> A.theme,
> A.receiver,
> A.dates
> FROM my_table A
> INNER JOIN my_table B
> ON A.theme=B.theme
> AND A.receiver=B.receiver
> AND A.date=ANY(B.dates)
> GROUP BY A.theme,A.receiver, A.dates;
>
> The result of the query is:
>
> sum | theme |receiver|
> dates
> ---+---+--+
> 3 | CRIS | +40741775622 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> 2 | CRIS | +40741775622 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> 3 | CRIS | +40741775622 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>18 | CRIS | +40741775622 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> 4 | LIA | +40741775621 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
> 4 | LIA | +40741775621 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> 6 | LIA | +40741775621 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>10 | LIA | +40741775621 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
>
> The result is wrong. I don't know what it is wrong at my query.
> Please help.
Loredana,
It is great to see your determination to get the answer, but we still do
not know what is "wrong" with the query result. You will need to
explain what you think is wrong before anyone can help. The output
looks like it matches the query perfectly.
Sean
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] CREATE RULE with WHERE clause
> CREATE RULE rule_role_sul AS
> ON SELECT TO t1 WHERE roles = 'role_sul'
> DO INSTEAD
> SELECT field1, field2 FROM t2;
>
> CREATE RULE rule_role_sul AS
> ON SELECT TO t1 WHERE roles = 'role_norte'
> DO INSTEAD
> SELECT field3, field4 FROM t2;
From: http://www.postgresql.org/docs/8.2/interactive/sql-createrule.html
...
WHERE condition
Any SQL conditional expression (returning boolean). The condition expression
may not refer to any
tables except NEW and OLD, and may not contain aggregate functions.
...
This statement to me implies that only ON {INSERT | UPDATE | DELETE } actions
can use the WHERE
syntax since only inserts, updates, and deletes product the NEW.* and OLD.*
tables.
Also, NEW.* and OLD.* only apply to a single tuple in the view that is being
changed. So I do not
think you can not use the where syntax in your query since it does not and
cannot reference a NEW
or OLD tuple.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] JOIN
On 6/5/07, Oliveiros Cristina <[EMAIL PROTECTED]> wrote:
Hey, Loredana.
Hi Oliveiros! Nice to "see" you again!
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that
appears on count for every record whose date is in dates column, is this
correct?
Yap.
But in what record's dates column? On all of them? Or just the dates column
of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
3
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output
for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver| date
|dates 2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
We can see that for LIA if we sum the count per day we have the following:
themedate count
LIA 2007-06-01 00:00:00+004
LIA 2007-06-02 00:00:00+002
LIA 2007-06-03 00:00:00+002
LIA 2007-06-04 00:00:00+002
Also for CRIS:
themedate count
CRIS 2007-06-01 00:00:00+003
CRIS 2007-06-02 00:00:00+001
CRIS 2007-06-03 00:00:00+001
CRIS 2007-06-04 00:00:00+003
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver | dates
-+--+-+
8 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA| +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA| +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA| +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA| +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
We can see that for example for the first row, the sum 8 it is correctly
calculated because if we sum the count for the days from dates column.
If we take a look at the fourth row we can see that the sum it is not
correct: it should be taken values for the count only for the date
2007-06-04.
The same problem it is at the eigth row.
Best regards,
Loredana
Re: [SQL] JOIN
On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote:
On 6/5/07, Oliveiros Cristina <[EMAIL PROTECTED]> wrote:
>
> Hey, Loredana.
Hi Oliveiros! Nice to "see" you again!
Please advice me,
> you need to sum for a certain pair (Theme, receiver) the number that
> appears on count for every record whose date is in dates column, is this
> correct?
Yap.
But in what record's dates column? On all of them? Or just the dates
> column of the records that have that (Theme , Receiver) ?
>
> Suppose I have
> 3| CRIS | rec1 | date1 | (date1,date2)
> 3| CRIS | rec1 | date2 | (date1,date3)
>
> What would be your intended sum?
> 3 ? 6 ?
3
date2 is not on dates column for that record, but it is on the first...
>
> Could you please show me an example of what would be the correct output
> for ex for ,
> CRIS | +40741775622 ?
> And For
> LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver| date
|dates 2 | LIA | +40741775621 | 2007-06-02 00:00:00+00
|
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
We can see that for LIA if we sum the count per day we have the following:
themedate count
LIA 2007-06-01 00:00:00+004
LIA 2007-06-02 00:00:00+002
LIA 2007-06-03 00:00:00+002
LIA 2007-06-04 00:00:00+002
Also for CRIS:
themedate count
CRIS 2007-06-01 00:00:00+003
CRIS 2007-06-02 00:00:00+001
CRIS 2007-06-03 00:00:00+001
CRIS 2007-06-04 00:00:00+003
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver |
dates
-+--+-+
8 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
5 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
10 | LIA| +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
6 | LIA| +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
4 | LIA| +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA| +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
We can see that for example for the first row, the sum 8 it is correctly
calculated because if we sum the count for the days from dates column.
If we take a look at the fourth row we can see that the sum it is not
correct: it should be taken values for the count only for the date
2007-06-04
The sum shoud be 3.
The same problem it is at the eigth row. The sum should
Re: [SQL] JOIN
Hey, Loredana.
Nice to "see" you too ;-)
Thank you for your detailed clarifications.
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY() returns
true if B.date is on the vector A.dates, is this correct??
Loredane, Then please let me hear bout the result
Best,
Oliveiros
2007/6/5, Loredana Curugiu <[EMAIL PROTECTED]>:
On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote:
>
>
>
> On 6/5/07, Oliveiros Cristina < [EMAIL PROTECTED]> wrote:
> >
> > Hey, Loredana.
>
>
> Hi Oliveiros! Nice to "see" you again!
>
> Please advice me,
> > you need to sum for a certain pair (Theme, receiver) the number that
> > appears on count for every record whose date is in dates column, is this
> > correct?
>
>
> Yap.
>
> But in what record's dates column? On all of them? Or just the dates
> > column of the records that have that (Theme , Receiver) ?
> >
> > Suppose I have
> > 3| CRIS | rec1 | date1 | (date1,date2)
> > 3| CRIS | rec1 | date2 | (date1,date3)
> >
> > What would be your intended sum?
> > 3 ? 6 ?
>
>
> 3
>
> date2 is not on dates column for that record, but it is on the first...
> >
> > Could you please show me an example of what would be the correct
> > output for ex for ,
> > CRIS | +40741775622 ?
> > And For
> > LIA | +40741775621 ?
>
>
> Let's take a look at the following data:
>
> count | theme | receiver| date
> |dates 2 | LIA | +40741775621 | 2007-06-02
> 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |
> 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |
> 3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> |
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
>|
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |
> 1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
> |
> 4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
> |
> 1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
> {2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> |
> 1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> |
>
>
> We can see that for LIA if we sum the count per day we have the
> following:
> themedate count
> LIA 2007-06-01 00:00:00+004
> LIA 2007-06-02 00:00:00+002
> LIA 2007-06-03 00:00:00+002
> LIA 2007-06-04 00:00:00+002
>
> Also for CRIS:
>
> themedate count
> CRIS 2007-06-01 00:00:00+003
> CRIS 2007-06-02 00:00:00+001
> CRIS 2007-06-03 00:00:00+001
> CRIS 2007-06-04 00:00:00+003
>
>
> With the following query
> SELECT SUM(B.count),
>A.theme,
>A.receiver,
>A.dates
> FROM view_sent_messages A
> INNER JOIN view_sent_messages B
> ON A.theme=B.theme
> AND A.receiver=B.receiver
> AND B.date=ANY (A.dates)
> GROUP BY A.theme,A.receiver, A.dates;
>
> I obtain the following result:
>
> sum | theme | receiver |
> dates
>
>
-+--+-+
> 8 | CRIS | +40741775622 |
> {2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
> 5 | CRIS | +40741775622 |
> {2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
> 4 | CRIS | +40741775622 |
> {2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
> 9
Re: [SQL] [GENERAL] CREATE RULE with WHERE clause
On Jun 5, 2007, at 8:11 , Ranieri Mazili wrote: CREATE RULE rule_role_sul AS ON SELECT TO t1 WHERE roles = 'role_sul' DO INSTEADSELECT field1, field2 FROM t2; CREATE RULE rule_role_sul AS ON SELECT TO t1 WHERE roles = 'role_norte' DO INSTEADSELECT field3, field4 FROM t2; I don't believe you can include a WHERE clause like this. From the documentation[1]: http://www.postgresql.org/docs/8.2/interactive/rules-views.html#RULES- SELECT Currently, there can be only one action in an ON SELECT rule, and it must be an unconditional SELECT action that is INSTEAD. This restriction was required to make rules safe enough to open them for ordinary users, and it restricts ON SELECT rules to act like views. You can use views instead (which are implemented using the rule system), but I'm not sure how you would handle it in this case. I believe you'd have to implement two views: CREATE VIEW rule_role_sul AS SELECT field1, field2 FROM t2 WHERE roles = 'role_sul'; CREATE VIEW rule_role_norte AS SELECT field3, field4 FROM t2 WHERE roles = 'role_norte'; Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] JOIN
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
Doesn't work. I get the result
sum | theme | receiver | dates
-++--+
3 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA| +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
2 | LIA| +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | LIA| +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA| +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
Which is not correct. The wrong values ( red colored ) remain as before
adding the clause. And now it is summed the counter's values per day
( first day of dates array ).
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY()
returns true if B.date is on the vector A.dates, is this correct??
Correct.
Regards,
Loredana
Re: [SQL] JOIN
Hello again, Loredana.
Additional information required :-)
imagine the following situation
1| LIA | recv1 | date1 | (date1,date2)
2|LIA | recv1 |date 1 | (date2,date3)
3| LIA | recv1 | date1 | (date1,date3)
Should this yield 6? Or 4?
date 1 is not on the second dates column, but it is on the remaining two
Cheers,
Oliveiros
- Original Message -
From: Loredana Curugiu
To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] ; pgsql-sql@postgresql.org
Sent: Tuesday, June 05, 2007 3:15 PM
Subject: Re: [SQL] JOIN
On 6/5/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote:
On 6/5/07, Oliveiros Cristina < [EMAIL PROTECTED]> wrote:
Hey, Loredana.
Hi Oliveiros! Nice to "see" you again!
Please advice me,
you need to sum for a certain pair (Theme, receiver) the number that
appears on count for every record whose date is in dates column, is this
correct?
Yap.
But in what record's dates column? On all of them? Or just the dates
column of the records that have that (Theme , Receiver) ?
Suppose I have
3| CRIS | rec1 | date1 | (date1,date2)
3| CRIS | rec1 | date2 | (date1,date3)
What would be your intended sum?
3 ? 6 ?
3
date2 is not on dates column for that record, but it is on the first...
Could you please show me an example of what would be the correct output
for ex for ,
CRIS | +40741775622 ?
And For
LIA | +40741775621 ?
Let's take a look at the following data:
count | theme | receiver| date |
dates
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
|
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
|
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
|
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08} |
We can see that for LIA if we sum the count per day we have the following:
themedate count
LIA 2007-06-01 00:00:00+004
LIA 2007-06-02 00:00:00+002
LIA 2007-06-03 00:00:00+002
LIA 2007-06-04 00:00:00+002
Also for CRIS:
themedate count
CRIS 2007-06-01 00:00:00+003
CRIS 2007-06-02 00:00:00+001
CRIS 2007-06-03 00:00:00+001
CRIS 2007-06-04 00:00:00+003
With the following query
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM view_sent_messages A
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme,A.receiver, A.dates;
I obtain the following result:
sum | theme | receiver | dates
-+--+-+
8 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
Re: [SQL] JOIN
On 6/5/07, Oliveiros Cristina <[EMAIL PROTECTED]> wrote: Hello again, Loredana. Additional information required :-) imagine the following situation 1| LIA | recv1 | date1 | (date1,date2) 2|LIA | recv1 |date 1 | (date2,date3) 3| LIA | recv1 | date1 | (date1,date3) Should this yield 6? Or 4? date 1 is not on the second dates column, but it is on the remaining two if the first column is count then the sum(count) should be 4. Loredana
Re: [SQL] JOIN
And , still, in your query, you are grouping by A.dates... is there any reason
for this that I am missing ?
SELECT SUM(A.count),
A.theme,
A.receiver,
A.dates
FROM my_table A
INNER JOIN my_table B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date=ANY(B.dates)
GROUP BY A.theme,A.receiver, A.dates;
If the dates column works as a "discriminator" to see if the row should be
considered or not,
maybe this would work
SELECT SUM(count), theme,receiver,date
FROM my_table
WHERE date=ANY(dates)
GROUP BY theme,receiver,date ;
But I don't know, do you need to include the column "dates" on output ?
Best,
Oliveiros
From: Loredana Curugiu
To: Oliveiros Cristina ; [EMAIL PROTECTED] ; pgsql-sql@postgresql.org
Sent: Tuesday, June 05, 2007 3:46 PM
Subject: Re: [SQL] JOIN
Hmm...try to add the following clause to your INNER JOIN
AND A.date = B.Date
Like this :
INNER JOIN view_sent_messages B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = b.Date
AND B.date=ANY (A.dates)
Doesn't work. I get the result
sum | theme | receiver | dates
-++--+
3 | CRIS | +40741775622 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
9 | CRIS | +40741775622 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA| +40741775621 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
2 | LIA| +40741775621 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | LIA| +40741775621 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
4 | LIA| +40741775621 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
Which is not correct. The wrong values ( red colored ) remain as before
adding the clause. And now it is summed the counter's values per day
( first day of dates array ).
I have not your data here, so I am not sure if it'll work.
Also, Ive never worked with vectors on Postgres. I am assuming ANY()
returns true if B.date is on the vector A.dates, is this correct??
Correct.
Regards,
Loredana
Re: [SQL] current_date / datetime stuff
We should have a onliner contest. I love oneliners!!!
oneliner:
select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc
('month',now()))||'days')::text)::interval;
Kristo
On 04.06.2007, at 19:39, Michael Glaesemann wrote:
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] current_date / datetime stuff
On 6/5/07, Gerardo Herzig <[EMAIL PROTECTED]> wrote: We should have a onliner contest. I love oneliners!!! +1 on that ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [GENERAL] Encrypted column
Marko Kreen wrote:
On 6/5/07, Brian Mathis <[EMAIL PROTECTED]> wrote:
pgcrypto also supports md5, so I'm not sure what you're referring to
here.
digest(psw, 'md5') vs. crypt(psw, gen_salt('md5'))
As I already mentioned, *salting* before you hash is a very
important step. I'm not sure if you saw that in my post. Without a
salt, it's trivial to generate a list of all combinations of md5'd
strings and their results, up to reasonable lengths. Then it would be
very simple to look up each hash and get the original text. With a
salt, you need to generate all possible md5s for all possible salts --
a much harder task.
I dont think its practical method tho'. Rather, when doing
dictionary-based or bruteforce attack, then if hashes do not
have salts you attack them all at once.
But if they have salts then for each word you try you need to
hash it for each salt. Which basically gives the effect that
each hash needs to be attacked separately.
In case of attacking one hash the salt does not matter,
only the algorithm counts then. In that case as i said,
event salted md5 is weaker than des-crypt.
The best method as far as I understand it is HMAC
(http://www.faqs.org/rfcs/rfc2104.html).
It has some significant cryptanalysis behind it to ensure it does not
leak information that would compromise the password. Even MD5 and SHA1,
which have been shown to have certain weaknesses, are not at issue when
used with HMAC (see, for example, section 3.1.1 of
http://www.apps.ietf.org/rfc/rfc4835.html)
The way you would use HMAC is:
1. generate a random token, whatever length you want (the salt)
2. use HMAC (implemented with either md5 or sha1 or something newer) to
hash the salt with the password
3. store the salt and the resulting HMAC hash
4. on login, calculate the HMAC of the token using the provide password,
and compare to the stored hash
pgcrypto appears to support HMAC. It is also relatively easy to
implement on top of the built in md5 function if you'd rather not
install pgcrypto. And I'm sure there are HMAC functions available that
could be used in PL/Perl and/or PL/Python.
Joe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] How to find missing values across multiple OUTER JOINs
I'm having troubles using multiple OUTER JOINs, which I think I want to use to solve my problem. My problem is to find all non-translated string values in our translations database, given the 4 following tables: SOURCE (source_id PRIMARY KEY, language_id, value, ...) TRANSLATION (translation_id PRIMARY KEY, language_id, value, ...) TRANSLATION_PAIR (source_id, translation_id) LANGUAGE(language_id PRIMARY KEY, name) This seems to me the appropriate situation for using OUTER JOINs, but I cannot figure out how to get the null rows without the not-null rows. Here's my best guess at this query: SELECT s.source_id,tp.translation_pair_id,t.translation_id,t.language_id, l.name FROM source s LEFT OUTER JOIN translation_pair tp USING(source_id) LEFT OUTER JOIN translation t ON tp.translation_id = t.translation_id AND t.translation_id is null) RIGHT OUTER JOIN language l on l.language_id = t.language_id; To test this query, I have a string that only has a translation in English and used it in this test query. SELECT s.source_id,tp.translation_pair_id,t.translation_id,t.language_id, l.name FROM (select * FROM source s WHERE source_id = 159986) as s LEFT OUTER JOIN translation_pair tp USING(source_id) LEFT OUTER JOIN translation t ON tp.translation_id = t.translation_id RIGHT OUTER JOIN language l on l.language_id = t.language_id; This yields promising results: source_id | translation_pair_id | translation_id | language_id | name ---+-++- +-- | || | Russian | || | Danish | || | Dutch 159986 | 1893187 |1743833 | 4 | English | || | Finnish | || | French | || | German | || | Italian | || | Japanese | || | Korean | || | Norwegian | || | Simplified Chinese | || | Spanish | || | Swedish | || | Traditional Chinese | || | Portuguese | || | Polish | || | Turkish | || | Czech | || | Brazilian Portuguese (20 rows) However, when I try to exclude the one not-null row, doing this: SELECT s.source_id,tp.translation_pair_id,t.translation_id,t.language_id, l.name FROM (select * FROM source s WHERE source_id = 159986) AS s LEFT OUTER JOIN translation_pair tp USING(source_id) LEFT OUTER JOIN translation t ON tp.translation_id = t.translation_id AND t.translation_id IS NULL RIGHT OUTER JOIN language l on l.language_id = t.language_id; I expect 19 rows, but instead get 20 rows, all containing null values. source_id | translation_pair_id | translation_id | language_id | name ---+-++- +-- | || | Russian | || | Danish | || | Dutch | || | English | || | Finnish | || | French | || | German | || | Italian | || | Japanese | || | Korean | || | Norwegian | || | Simplified Chinese |
