backup and store oids

2. When backup my database use:
pg_dump dbname > dbname.pgdump

Use: "pg_dump -o dbname > dbname.pgdump" to dump the oid's with the rest
of the data.

Poul L. Christiansen

Hi.

I'm trying to get an average value (avg()) of a certain attribute in a
table. The snag is, I don't want this across a whole table. I only want it
over a certain set of records. In effect, I want to do something like:

SELECT somefield
FROM sometable
WHERE otherfield = 'criteria'
ORDER BY thirdfield
LIMIT 10

and then do an avg(somefield).

Can this be done without using temp tables, in a single query? I tried
making a function that does this, but even if I used temp tables, the
function didn't work, claiming that the table didn't exist.

Ideally, I want to do something like:

SELECT avg
(
SELECT somefield
FROM sometable
WHERE otherfield = 'criteria'
ORDER BY thirdfield
LIMIT 10
) as somefieldname

But the parser doesn't seem to like it.

How can I do this?

Thanks.

Gordan

Gabriel Lopez <gabilm@dif.um.es> writes:

1. I have problem insert oid object in a table, not always,
sometimes. I have the exception
FastPath call returned FATAL 1: my bits moved right off
Recreate index pg_attribute_relid_attnum_index.

We've seen that reported before. It suggests that there's some corner-
case bug lurking in the btree index routines. We could find and fix it
given a reproducible test case ... but no one's been able to provide a
test case. Can you?

regards, tom lane

"Gordan Bobic" <gordan@freeuk.com> writes:

over a certain set of records. In effect, I want to do something like:

SELECT somefield
FROM sometable
WHERE otherfield = 'criteria'
ORDER BY thirdfield
LIMIT 10

and then do an avg(somefield).

Can this be done without using temp tables, in a single query?

Not if the order by/limit are essential to selecting the rows you need
to average over. At least not in 7.0 ... in 7.1 this'll work:

SELECT avg(somefield) FROM
(SELECT somefield FROM sometable ... LIMIT 10) t1;

For the moment a temp table is the way to go.

regards, tom lane

How about

SELECT
SUM(CASE WHEN otherfield = 'criteria' THEN 1 ELSE 0 END) as count_comply,
SUM(CASE WHEN otherfield = 'criteria' THEN somefield ELSE 0 END) as
sum_comply,
sum_comply/count_comply AS mean_comply
FROM sometable
ORDER BY thirdfield
LIMIT 10

?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Thursday, November 30, 2000 3:39 PM
To: Gordan Bobic
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Built in Functions use with recordsets

"Gordan Bobic" <gordan@freeuk.com> writes:

over a certain set of records. In effect, I want to do something like:

SELECT somefield
FROM sometable
WHERE otherfield = 'criteria'
ORDER BY thirdfield
LIMIT 10

and then do an avg(somefield).

Can this be done without using temp tables, in a single query?

Not if the order by/limit are essential to selecting the rows you need
to average over. At least not in 7.0 ... in 7.1 this'll work:

SELECT avg(somefield) FROM
(SELECT somefield FROM sometable ... LIMIT 10) t1;

For the moment a temp table is the way to go.

regards, tom lane