[GENERAL] Cartesian product not correct

[Angus Miller]

Hi

Given the sql below shouldn't I get the 3 records back?

create table t1 (attribute text);
insert into t1 values ('cars');
insert into t1 values ('trucks');
insert into t1 values ('bikes');

create table t2 (id serial, category text, attribute text, val integer);
insert into t2(category, attribute, val) values ('vehicle','cars', 100);

select t2.category, t1.attribute, 0 as val
from t2, t1
where t2.id in (
 select max(id)
 from t2
 group by category
);


PostgreSQL 8.4.0 on windows

Cheers
Angus


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cartesian product not correct

[Angus Miller]

Thanks Tom I can confirm this passed in 8.4.1

Tom Lane wrote:

Angus Miller  writes:
  

Given the sql below shouldn't I get the 3 records back?



Try 8.4.1 --- I think you got bit by the semijoin ordering bugs in
8.4.0.

regards, tom lane
  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Paul prefers Postgres

[Angus Miller]

I couldn't resist... http://bit.ly/aks7og

[GENERAL] Temporal'sh type sql question

[Angus Miller]

Hi

I have a table that models data in a temporal fashion. I say temporal
fashion as I'm not sure I got this 100% right buy here goes. Basically
each record has a date_from and date_to column. The date_from column
always defaults to the current system date. The date_to column is
either a far future value or the date that that records life-cycle
ended.

For simplicities sake lets say I'm recording some configuration data
that is relatively static. Now there is a need to report on the
changes per day. The temporal data is only storing new events when
they happen, maybe one every 10-15 days.

How can I generate result-set that will carry the previous records
values through till a new event occurred so there is a record for each
"day"?

Cheers
Angus

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Temporal'sh type sql question

[Angus Miller]

Thanks Sergery that's what I was after.


1 2010/01/01

On Mon, Sep 6, 2010 at 2:29 PM, Sergey Konoplev  wrote:
> Hi,
>
> On 6 September 2010 16:18, Angus Miller  wrote:
>> How can I generate result-set that will carry the previous records
>> values through till a new event occurred so there is a record for each
>> "day"?
>
> If I understand the problem correct you need to use generate_series()
> function (http://www.postgresql.org/docs/8.4/interactive/functions-srf.html).
> Just join your data to its result set.
>
>>
>> Cheers
>> Angus
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> Sergey Konoplev
>
> Blog: http://gray-hemp.blogspot.com /
> Linkedin: http://ru.linkedin.com/in/grayhemp /
> JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general