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 [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
