Hello,
First of all: I' m using postgresql v7.0.2 on debian 2.2. (and I have two
completely different questions)
i) What is the most effective representation of
type "set" in postgres?
As I remember there is no attribute type to
represent definitely sets. (Please, correct me
if I'm wrong.)
Arrays and bitstrings seem to serve this
purpose, but what about performance regarding to the commonly
used set operation (is in, union, etc)?
ii) When I execute the following
query:
=> INSERT INTO stat (vendor_id, c_date, c_num)
SELECT vendor_id, current_date, count(*) FROM device_id in (...) GROUP BY
vendor_id;
I get an error message something like
"ExecAggrEval(), aggregate function is not available here".
(this is surely not what I get back if it's needed
I post the right message )
I know what cause the problem...
I create a rule on table stat that turns an
INSERT query into UPDATE if there's already a record with the same vendor_id and
c_date.
=> CREATE RULE r_logstat AS ON INSERT TO
stat
-> WHERE EXISTS (SELECT * FROM stat
WHERE vendor_id = new.vendor_id AND c_date= new.c_date)
-> DO INSTEAD UPDATE stat SET c_num =
c_num + new.c_num WHERE vendor_id = new.vendor_id AND c_date=
new.c_date;
if the rule is not invoked everything goes fine,
but if there is a row that should be updated instead of INSERT the
query fails.
How can I solve this ? Maybe an upgrade to v7.1
blows the whole thing out?
- Papp Gyozo - |
- Re: [GENERAL] sets and insert-select with rule Gyozo Papp
- Re: [GENERAL] sets and insert-select with rule Tom Lane
- [GENERAL] 7.1 dumps with large objects David Wall
- Re: [GENERAL] 7.1 dumps with large objects Tom Larard
- [GENERAL] insert-select once more Gyozo Papp