\copy ignoring Rules Was [Re: Insert or Replace or \copy (bulkload)]

On Mon, 2007-08-27 at 11:55 +0800, Ow Mun Heng wrote:
> I just ran into trouble with this. This rule seems to work when I do
> simple inserts, but as what I will be doing will be doing \copy
> bulkloads, it will balk and fail.
> Now would be a good idea to teach me how to skin the cat differently.

Base on the arhives, seems like this was raised as BUG 2437 close to a
year ago and it was marked as "correct behaviour" and the suggested
alternative is to either

1. Use pgloader (DL'ed it, but have not gone indepth to see if it suits
my needs)

2. COPY into temp table, and then perform an insert into.

=>create table foo (a int, b int, c int, d varchar(1) e bigint);
=>alter table foo add primary key (a,b,c,e);
=>insert into foo values(1,1,1,'A',1);
=>create rule replace_foo as on insert to foo where (exists(select 1
from foo where foo.a = new.a and foo.b = new.b and foo.c = new.c and
foo.e = new.e)) do instead update foo set d = new.d where foo.a = new.a
and foo.b = new.b and foo.c = new.c and foo.e = new.e
=> \d foo;
Table "xmms.foo"
Column | Type | Modifiers
--------+----------------------+-----------
a | integer | not null
b | integer | not null
c | integer | not null
d | character varying(1) |
e | bigint | not null
Indexes:
"foo_pkey" PRIMARY KEY, btree (a, b, c, e)
Rules:
replace_foo AS
ON INSERT TO foo
WHERE (EXISTS ( SELECT 1
FROM foo
WHERE foo.a = new.a AND foo.b = new.b AND foo.c = new.c AND
foo.e = new.e)) DO INSTEAD UPDATE foo SET d = new.d
WHERE foo.a = new.a AND foo.b = new.b AND foo.c = new.c AND foo.e =
new.e
=>create table temp_table_foo as select * from foo where 1=0;

=> select * from foo;
a | b | c | d | e
---+---+---+---+---
1 | 1 | 1 | A | 1

=>insert into temp_table_foo values(1,1,1,'Z',1);

=> select * from foo;
a | b | c | d | e
---+---+---+---+---
1 | 1 | 1 | Z | 1

=> truncate table foo

Seems like an awful lot of step and may even make the \copy process even
longer than it should.. Is there any other method? Seems to me pgloader
also does more or less the same thing..

What about pgbulkload? Anyone can provide any new insights?

Thanks...

http://archives.postgresql.org/pgsql-bugs/2006-05/msg00073.php