Thread: strange query results
hi guys
I am trying out some relativly simple queries against my database..
select distinct site_section as "distinct site sections" from exhibit_distributions ;
distinct site sections
------------------------
ARCHIVED
ARTETC
CALENDAR
GALLERY
POSTCARD
(5 rows)
select site_section, count(*) from exhibit_distributions group by site_section;
site_section | count
--------------+-------
| 352
| 45
| 1
| 166
| 2
The second query is not priniting out site_section column.. This is
happening in 3 seperate dbs (702 and 703).. Can any one point out the
mistake.
This is proving to be a show stopper .. We arent able to select rows for
a particular site_section..
Thanks for your response
Anand
Anand Raman <araman@india-today.com> writes:
> The second query is not priniting out site_section column.
Odd. What is the exact definition of table exhibit_distributions?
Does it have any indices? What plan is printed by EXPLAIN for the
problem query?
regards, tom lane
Hi tom
The table description is as follows
arttoday=> \d exhibit_distributions
Table "exhibit_distributions"
Attribute | Type | Modifier
------------------------------+---------------+------------------------------------------------------
exhibit_distribution_id | integer | not null default nextval('sq_exhibit_dist_id'::text)
exhibit_id | integer | not null
created_by | integer | not null
creation_date | timestamp | not null default "timestamp"('now'::text)
last_update_date | timestamp |
last_updated_by | integer |
exhibit_type_id | integer | not null
medium | varchar(100) |
image_path_small | varchar(50) |
image_path_big | varchar(50) |
length | numeric(7,2) |
breadth | numeric(7,2) |
width | numeric(7,2) |
diameter | numeric(7,2) | default 8
dimensional_aspect | varchar(50) | default 'Dimensions'
unframed_volume_weight | numeric(10,2) |
framed_volume_weight | numeric(10,2) |
override_vw_computation | boolean | default 'f'::bool
exhibit_code | varchar(25) |
exhibit_options | varchar(10) |
unframed_exhibit_restriction | varchar(25) | default 'WORLD'
framed_exhibit_restriction | varchar(25) | default 'WORLD'
up_for_sale | char(1) | not null default 'T'
gallery_id | integer |
site_section | varchar(20) | not null default 'GALLERY'
Index: exhibit_distributions_pkey
Constraints: ((up_for_sale = 'T'::bpchar) OR (up_for_sale = 'F'::bpchar))
(length > '0'::"numeric")
(breadth > '0'::"numeric")
(width > '0'::"numeric")
(((unframed_exhibit_restriction = 'WORLD'::"varchar") OR (unframed_exhibit_restriction =
'INDIA'::"varchar"))OR (unframed_exhibit_restriction = 'ONLY_WORLD'::"varchar"))
(((framed_exhibit_restriction = 'WORLD'::"varchar") OR (framed_exhibit_restriction = 'INDIA'::"varchar"))
OR(framed_exhibit_restriction = 'ONLY_WORLD'::"varchar"))
(((exhibit_options = 'FRAMED'::"varchar") OR (exhibit_options = 'UNFRAMED'::"varchar")) OR
(exhibit_options= NULL::"varchar"))
Explain plan results in the following
arttoday=> explain select site_section, count(*) from exhibit_distributions group by site_section;
NOTICE: QUERY PLAN:
Aggregate (cost=69.83..74.83 rows=100 width=12)
-> Group (cost=69.83..72.33 rows=1000 width=12)
-> Sort (cost=69.83..69.83 rows=1000 width=12)
-> Seq Scan on exhibit_distributions
(cost=0.00..20.00 rows=1000 width=12)
The problem still persists.. I will a drop and recreation of the db just
in case..
Thanks for the help
Anand
On Mon, Feb 12, 2001 at 11:04:55AM -0500, Tom Lane wrote:
>Anand Raman <araman@india-today.com> writes:
>> The second query is not priniting out site_section column.
>
>Odd. What is the exact definition of table exhibit_distributions?
>Does it have any indices? What plan is printed by EXPLAIN for the
>problem query?
>
> regards, tom lane
Hi tom Thanks for your time.. No all of the columns were there right from the start.. Even if i added a few columns i always went thru the process of dropping and recreating the entire db.. However a few days back there was a instance of index curroption and things came to a halt.. A vaccum of the database notified of the possible curroption and i recreated a index which didnt belong to this table.. I havent tried vaccuming the table now.. Will it help?? Thanks Anand On Tue, Feb 13, 2001 at 10:20:53AM -0500, Tom Lane wrote: >Anand Raman <araman@india-today.com> writes: >> The table description is as follows > >Hmm ... nothing obviously funny here. Is there anything unusual about >the history of this table? (For example, were site_section or any other >columns added via ALTER TABLE, rather than being there all along?) > > regards, tom lane
Anand Raman <araman@india-today.com> writes:
> The table description is as follows
Hmm ... nothing obviously funny here. Is there anything unusual about
the history of this table? (For example, were site_section or any other
columns added via ALTER TABLE, rather than being there all along?)
regards, tom lane
HI tom
A few days back i had bugged this list about the seemingly impossible
select queries results..
##RECAP##
select distinct site_section as "distinct site sections" from
exhibit_distributions ;
distinct site sections
------------------------
ARCHIVED
ARTETC
CALENDAR
GALLERY
POSTCARD
(5 rows)
select site_section, count(*) from exhibit_distributions group by
site_section;
site_section | count
--------------+-------
| 352
| 45
| 1
| 166
| 2
##RECAP##
On going thru the flat files which we used to uplaod the database we
noticed a few fields had '' characters to signify '.. This was creating
problems in some jdbc queries.
One changing them to single ' and reloading all the data, the problem
simply disappered..
Thanks for the help
Anand Raman
On Tue, Feb 13, 2001 at 10:20:53AM -0500, Tom Lane wrote:
>Anand Raman <araman@india-today.com> writes:
>> The table description is as follows
>
>Hmm ... nothing obviously funny here. Is there anything unusual about
>the history of this table? (For example, were site_section or any other
>columns added via ALTER TABLE, rather than being there all along?)
>
> regards, tom lane