pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinteger attribute 1672 != 1672
6+ messages / 3 participants
[nested] [flat]
* integer attribute 1672 != 1672
@ 2001-11-12 14:47 Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 15:35 ` Re: integer attribute 1672 != 1672 Tom Lane <tgl@sss.pgh.pa.us>
2001-11-12 16:05 ` Re: integer attribute 1672 != 1672 Haller Christoph <ch@rodos.fzk.de>
0 siblings, 2 replies; 6+ messages in thread
From: Markus Bertheau @ 2001-11-12 14:47 UTC (permalink / raw)
To: pgsql-sql
Good day,
cenes=> select version();
version
---------------------------------------------------------------
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)
cenes=> \d partner_views
Table "partner_views"
Attribute | Type | Modifier
------------+-----------+----------
partner_id | integer | not null
timestamp | timestamp | not null
Indices: partner_id_partner_views_key,
timestamp_partner_views_key
cenes=> select count(partner_id), partner_id from partner_views group by
partner_id order by partner_id;
count | partner_id
--------+------------
21473 | 0
1 | 1672
116636 | 0
266 | 1670
17991 | 1672
1 | 0
79614 | 1672
3723 | 1677
39 | 1692
30150 | 1814
3853 | 1874
4951 | 1905
2 | 1948
28 | 2101
241 | 2129
(15 rows)
how can this be? is it a known bug which is fixed in 7.1.3?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: integer attribute 1672 != 1672
2001-11-12 14:47 integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
@ 2001-11-12 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-12 15:52 ` Re: integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2001-11-12 15:35 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
Markus Bertheau <twanger@bluetwanger.de> writes:
> PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
Grouped views don't really work in any version before 7.1.
Time to update ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: integer attribute 1672 != 1672
2001-11-12 14:47 integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 15:35 ` Re: integer attribute 1672 != 1672 Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-11-12 15:52 ` Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 15:59 ` Re: integer attribute 1672 != 1672 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Markus Bertheau @ 2001-11-12 15:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Good day,
> Grouped views don't really work in any version before 7.1.
cenes=> select version();
version
---------------------------------------------------------------
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)
cenes=> select distinct partner_id from partner_views;
partner_id
------------
0
1672
0
1670
1672
0
1672
1677
1692
1814
1874
1905
1948
2101
2129
(15 rows)
same thing?
Markus Bertheau
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: integer attribute 1672 != 1672
2001-11-12 14:47 integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 15:35 ` Re: integer attribute 1672 != 1672 Tom Lane <tgl@sss.pgh.pa.us>
2001-11-12 15:52 ` Re: integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
@ 2001-11-12 15:59 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2001-11-12 15:59 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
Markus Bertheau <twanger@bluetwanger.de> writes:
> same thing?
If it's a view that contains internal grouping/aggregating, yes.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: integer attribute 1672 != 1672
2001-11-12 14:47 integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
@ 2001-11-12 16:05 ` Haller Christoph <ch@rodos.fzk.de>
2001-11-12 15:55 ` Re: integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 6+ messages in thread
From: Haller Christoph @ 2001-11-12 16:05 UTC (permalink / raw)
To: twanger@bluetwanger.de; +Cc: pgsql-sql
Hi,
This is not a bug.
I think it's a mistake in understanding
aggregate functions and grouping.
A typical example for a count/group operation
would be
Tell me about the number of employees in each department.
in SQL
select count(employee), department from employee_table
group by depatment ;
AFAIK if you use an aggregate function on a column and
group by this column the result is undefined.
Re-think what you want to query.
Regards, Christoph
>
> cenes=> \d partner_views
> Table "partner_views"
> Attribute | Type | Modifier
> ------------+-----------+----------
> partner_id | integer | not null
> timestamp | timestamp | not null
> Indices: partner_id_partner_views_key,
> timestamp_partner_views_key
>
> cenes=> select count(partner_id), partner_id from partner_views group by
> partner_id order by partner_id;
> count | partner_id
> --------+------------
> 21473 | 0
> 1 | 1672
> 116636 | 0
> 266 | 1670
> 17991 | 1672
> 1 | 0
> 79614 | 1672
> 3723 | 1677
> 39 | 1692
> 30150 | 1814
> 3853 | 1874
> 4951 | 1905
> 2 | 1948
> 28 | 2101
> 241 | 2129
> (15 rows)
>
> how can this be? is it a known bug which is fixed in 7.1.3?
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: integer attribute 1672 != 1672
2001-11-12 14:47 integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 16:05 ` Re: integer attribute 1672 != 1672 Haller Christoph <ch@rodos.fzk.de>
@ 2001-11-12 15:55 ` Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 0 replies; 6+ messages in thread
From: Markus Bertheau @ 2001-11-12 15:55 UTC (permalink / raw)
To: Haller Christoph <ch@rodos.fzk.de>; pgsql-sql
> AFAIK if you use an aggregate function on a column and
> group by this column the result is undefined.
> Re-think what you want to query.
For each view there is a row in partner_views (the partner_id and a
timestamp). I want to know how many views each partner has. I think the
query is semantically correct.
Markus Bertheau
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-11-12 16:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-12 14:47 integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-12 15:52 ` Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 15:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-12 16:05 ` Haller Christoph <ch@rodos.fzk.de>
2001-11-12 15:55 ` Markus Bertheau <twanger@bluetwanger.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox