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 -