My guess is that when you insert into Cities, the sequence field is
incremented one time (=2), and when the Rule is executed to insert
into Capital, the sequence is incremented a second time (=3). As a
result of these 2 operations, the sequence is incremented 2 times.
I don't know anything about your application or business rules, but
your solution seems unnecessarily complicated. Remember, the best and
smartest approach is usually the simplest one. You can do this in a
much simpler way.
On 4/4/07, Huynh Ngoc Doan <hndoan@xxxxxxxxxxxxxxxxxx> wrote:
/*I'm learning how to use the partion in Postgres 8.2.3 so I want to
do some test for my awareness.
I create 2 tables:
The parent table is cities:*/
CREATE TABLE xxx.cities
(
id serial NOT NULL,
name text,
population real,
altitude integer,
CONSTRAINT pk_cities PRIMARY KEY (id)
)
/*and a partion of cities is capital:*/
CREATE TABLE xxx.capital
(
CONSTRAINT pk_capital PRIMARY KEY (id),
CONSTRAINT capital_id_check CHECK (id > 3)
) INHERITS (xxx.cities)
/*My purpose is that the cities will contain the rows having id <= 3
and the capital will contain the rows having id >=3.
So I create a rule cities_insert_capital to redirect data to the
table capital when new row's id >3
Here it is:
*/
CREATE OR REPLACE RULE cities_insert_capital AS
ON INSERT TO xxx.cities
WHERE new.id > 3 DO INSTEAD INSERT INTO xxx.capital(id,name,
population, altitude) VALUES (new.id,new.name, new.population, new.altitude)
After completing the reparation stage, I insert some rows into cities table:
--restart the serial value to 1
alter sequence xxx.cities_id_seq restart with 1;
--serial is 1 at this time
--1.
INSERT INTO xxx.cities(name, population, altitude)
VALUES('HCM',10,10);
--data is inserted into cities table as the result, serial is 3,and
the expected value is 2 =>what wrong here?
--2.Try more insert
INSERT INTO xxx.cities(name, population, altitude)
VALUES('Ha Noi',10,10);
--data is inserted into capital while it's is expected to insert to cities.
--serial is 5 at this time,and expected value is 3 => what wrong here?
/*Conclusion:
The serial increases by 2 when excuting one insert command.I try many
ways to find out the reason why, but I can't.
Can you help me to explain this unexpected behavior and give me some
advices to deal with this problem.
I want that the serial will increase by 1 unit after a insert comand.
Thank you very much.
Note:
I want id will be add automatically when a row is inserted.
*/
Best regard,
Doan. From VietNam
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
