In RULEs, INSERT does not use DEFAULTs - Mailing list pgsql-hackers

Folks,

At one time, INSERTing a NULL into a column with a DEFAULT used to
INSERT the DEFAULT.  Is there some way to get this behavior back?  I
think that this is a bug introduced by removing the NULL -> DEFAULT
behavior and not replacing it somehow.  Below is a simple repro.

I think this is a bug.  Although there are workarounds, the ones I've
found have been clumsy and foot-gun-ish.  Is there some way to fix
this?

Cheers,
D

CREATE TABLE foo ( foo_id SERIAL PRIMARY KEY
, foo_truth BOOLEAN NOT NULL DEFAULT TRUE
, foo_text TEXT
);

CREATE VIEW foo_caps AS
SELECT foo_id, foo_truth, UPPER(foo_text) AS "foo_text_cap"
FROM foo;

/* The naïf way */
CREATE RULE foo_caps_insert AS
ON INSERT
TO foo_caps
DO INSTEAD   INSERT INTO foo (foo_id, foo_truth, foo_text)   VALUES (NEW.foo_id, NEW.foo_truth, NEW.foo_text_cap);

INSERT INTO foo_caps (foo_truth)
VALUES (false);
ERROR:  null value in column "foo_id" violates not-null constraint

/* Possibly less naïf, but... */

DROP RULE foo_caps_insert ON foo_caps;

CREATE RULE foo_caps_insert AS
ON INSERT
TO foo_caps
DO INSTEAD   INSERT INTO foo (       foo_id,       foo_truth,       foo_text   ) VALUES (       COALESCE(NEW.foo_id,
DEFAULT),      COALESCE(NEW.foo_truth, DEFAULT),       NEW.foo_text_cap   );
 

ERROR:  syntax error at or near "DEFAULT" at character 183
LINE 10:         COALESCE(NEW.foo_id, DEFAULT),                                     ^
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!