/*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
