Achtung bei folgender Konstellation:
[sql]
CREATE TABLE source
(
id serial NOT NULL,
“text” character varying(255),
CONSTRAINT id_pkeyPRIMARY KEY (id)
)
[/sql]

mit Regel:
[sql]
CREATE RULE “insert_rule” AS
ON INSERT TO source DO  INSERT INTO dest (sourceid)
VALUES (NEW.id);
[/sql]

Serial und Bigserial sind in Postgres intern so konzipiert, dass der Wert für das Feld durch einen Aufruf von
[sql]
nextval(‘source_id_seq’)
[/sql]
erzeugt wird. Entgegen der Erwartung wird nach Ausführen der Regel aber nicht source.id in die Tabelle dest eingetragen, sondern source.id +1. Bug oder einfach so gewolltes Verhalten von Postgres? In der Postgres-Dokumentation zu Regeln findet sich zumindest ein entsprechender Kommentar:
“Beware that CREATE RULE insert_foo AS ON INSERT TO foos DO ALSO INSERT INTO bar (foo_id) VALUES (NEW.id); won’t work if foo.id is a serial (and foo_id REFERENCES foos ofcourse) because nextval() is evaluated twice.”

Sauberste Lösung: Hier keine Regel, sondern einen Trigger verwenden.

Postgres, Serials and Rules: Caution!

Caution with the following constellations:
[sql]
CREATE TABLE source
(
id serial NOT NULL,
“text” character varying(255),
CONSTRAINT id_pkeyPRIMARY KEY (id)
)
[/sql]

with rule:
[sql]
CREATE RULE “insert_rule” AS
ON INSERT TO source DO  INSERT INTO dest (sourceid)
VALUES (NEW.id);
[/sql]

Serial and Bigserial are so in Postgres internally designed that the data for the field can be generated with the request of
[sql]
nextval(‘source_id_seq’)
[/sql]
Against all expectations after carrying out the rule it is not source.id which is filled into the table dest but source.id +1. Bug or simply unwanted behavior of Postgres? In the Postgres-Documentation the rules can be found at least in on depending commentary:
“Beware that CREATE RULE insert_foo AS ON INSERT TO foos DO ALSO INSERT INTO bar (foo_id) VALUES (NEW.id); won’t work if foo.id is a serial (and foo_id REFERENCES foos ofcourse) because nextval() is evaluated twice.”

Cleanest solution: Do not use a rule here but a trigger.