From: | "Postgres User" <postgres(dot)developer(at)gmail(dot)com> |
---|---|
To: | "Huynh Ngoc Doan" <hndoan(at)ghp-fareast(dot)com(dot)vn> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Need your help on using "partion" |
Date: | 2007-04-05 03:33:44 |
Message-ID: | b88c3460704042033i213827aag11ecc6bf3e816477@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)ghp-fareast(dot)com(dot)vn> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Postgres User | 2007-04-05 03:38:19 | Re: Storing blobs in PG DB |
Previous Message | Postgres User | 2007-04-05 03:24:29 | Re: newid() in postgres |