> On 14 Feb 2018, at 2:48, DrakoRod <drakoflames@hotmail.com> wrote:
> CREATE OR REPLACE RULE inserts_customer_part1
> AS ON INSERT TO customers
> WHERE new.id < 10000
> DO INSTEAD INSERT INTO customers_part1 SELECT NEW.*;
>
> CREATE OR REPLACE RULE inserts_customer_part2
> AS ON INSERT TO customers
> WHERE new.id >= 10000 AND new.id < 20000
> DO INSTEAD INSERT INTO customers_part2 SELECT NEW.*;
Here's your problem. Rules substitute values. Since you didn't provide an id in your insert, the id column gets
substitutedby the default value, which happens to call nextval. You have 3 references to new.id in your rules, so the
sequenceincrements by 3.
That's one of the reasons people usually advise to use triggers & procedures instead of rules.
> dd=# SELECT * FROM customers;
> id | name | other_data
> ----+---------+------------
> 3 | XXXXXXx | YYYYYYYYYY
> 7 | XXXXXXx | YYYYYYYYYY
> 11 | XXXXXXx | YYYYYYYYYY
> 15 | XXXXXXx | YYYYYYYYYY
> 19 | XXXXXXx | YYYYYYYYYY
> 23 | XXXXXXx | YYYYYYYYYY
> (6 rows)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.