Column defaults fail with rules on view

[btober]

I'm finding that column defaults are not being assigned to nulls when I
do an insert by way of a an ON INSERT rule on a view. For example, the
following script

\set ON_ERROR_STOP ON

\c template1
--DROP DATABASE testdb;
CREATE DATABASE testdb;
\c testdb

create table test_table (
field1 char(1) not null,
field2 serial,
field3 integer default 1,
field4 varchar(24) default '(default value)',
constraint testdb_pkey primary key (field2));

INSERT INTO test_table VALUES ('A');

SELECT * FROM test_table;

CREATE VIEW test_table_v AS
SELECT field1, field3, field4 FROM test_table;

SELECT * FROM test_table;

CREATE RULE test_table_rd AS ON DELETE TO test_table_v DO INSTEAD
DELETE FROM test_table WHERE field1 = old.field1;

CREATE RULE test_table_ri AS ON INSERT TO test_table_v DO INSTEAD
INSERT INTO test_table (field1, field3, field4)
VALUES (new.field1, new.field3, new.field4);

CREATE RULE test_table_ru AS ON UPDATE TO test_table_v DO INSTEAD
UPDATE test_table SET
field1 = new.field1,
field3 = new.field3,
field4 = new.field4
WHERE field1 = old.field1;

INSERT INTO test_table_v VALUES ('B');

SELECT * FROM test_table;

-- produces this output

CREATE DATABASE
CREATE TABLE
INSERT 147461 1
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
(1 row)

-- above works fine, but then

CREATE VIEW
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
(1 row)

CREATE RULE
CREATE RULE
CREATE RULE
INSERT 147468 1
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
B | 2 | |
(2 rows)

-- notice how field3 and field4 are not assigned their defaults for row B!

Is this supposed to work that way? I would expect field3 and field4 to
have their respective column defaults assigned on the second INSERT (row
B), just like on the first INSERT (row A).

wassup wit dat?

~Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

On Friday 19 September 2003 09:00, bt****@seaworth ysys.com wrote:

I'm finding that column defaults are not being assigned to nulls when I
do an insert by way of a an ON INSERT rule on a view. For example, the
following script [snip] CREATE RULE test_table_ri AS ON INSERT TO test_table_v DO INSTEAD
INSERT INTO test_table (field1, field3, field4)
VALUES (new.field1, new.field3, new.field4); [snip] Is this supposed to work that way? I would expect field3 and field4 to
have their respective column defaults assigned on the second INSERT (row
B), just like on the first INSERT (row A).

Hmm - well, you're explicitly telling it to insert VALUES (..., new.field3,
....) so if new.field3 is null then it *should* do that.

Now - how you should go about getting the default I don't know. You could
build a rule with WHERE NEW.field3 IS NULL and then not pass field3, but that
would stop you explicitly setting it to null.

Out of curiosity, can you tell me what happens if you insert into the view
('C',DEFAULT,DE FAULT)?
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[btober]

On Friday 19 September 2003 09:00, bt****@seaworth ysys.com wrote:

I'm finding that column defaults are not being assigned to nulls when
I do an insert by way of a an ON INSERT rule on a view. For example,
the following script [snip]

Hmm - well, you're explicitly telling it to insert VALUES (...,
new.field3, ...) so if new.field3 is null then it *should* do that.

I (apparently mistakenly) thought that the point of specifying DEFAULT
values in the table column definition was so that the default value would
be inserted automatically rather than a null. And anyway, that IS how it
seems to work when I do the insert for row A to the table directly with

INSERT INTO test_table VALUES ('A');

where fields 2, 3, and 4 have not been assigned values. Why do they get
the default in this case?

Now - how you should go about getting the default I don't know. You
could build a rule with WHERE NEW.field3 IS NULL and then not pass
field3, but that would stop you explicitly setting it to null.
My work-around has been to define BEFORE INSERT triggers with lines like

SELECT INTO new.field3 COALESCE(new.fi eld3, 1);

testing for and optionally assigning the default, but I really don't like
having to explicitly do that for every table and NOT NULL column, since I
make pretty much routine use of RULES on VIEWS to make writeable views
the interface to my user application.

Out of curiosity, can you tell me what happens if you insert into the
view ('C',DEFAULT,DE FAULT)?
Richard Huxton

Same script, but with
INSERT INTO test_table VALUES ('A');
INSERT INTO test_table_v VALUES ('B');
INSERT INTO test_table_v VALUES ('C', DEFAULT, DEFAULT);

gives

field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
B | 2 | |
C | 3 | |
(3 rows)

-- so no change in behavior. I notice that field2, which was declared
type SERIAL, and so also has a DEFAULT, but one which calls the nextval
function rather than simply assigning a value, gets its default value
assigned in both the table insert and the view insert.

~Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

On Friday 19 September 2003 13:36, bt****@seaworth ysys.com wrote:

On Friday 19 September 2003 09:00, bt****@seaworth ysys.com wrote:

I'm finding that column defaults are not being assigned to nulls when
I do an insert by way of a an ON INSERT rule on a view. For example,
the following script

[snip]

Hmm - well, you're explicitly telling it to insert VALUES (...,
new.field3, ...) so if new.field3 is null then it *should* do that.

I (apparently mistakenly) thought that the point of specifying DEFAULT
values in the table column definition was so that the default value would
be inserted automatically rather than a null. And anyway, that IS how it
seems to work when I do the insert for row A to the table directly with

INSERT INTO test_table VALUES ('A');

where fields 2, 3, and 4 have not been assigned values. Why do they get
the default in this case?

Because you're not doing
INSERT INTO test_table VALUES ('A',null,null)

Now - how you should go about getting the default I don't know. You
could build a rule with WHERE NEW.field3 IS NULL and then not pass
field3, but that would stop you explicitly setting it to null.

My work-around has been to define BEFORE INSERT triggers with lines like

SELECT INTO new.field3 COALESCE(new.fi eld3, 1);

testing for and optionally assigning the default, but I really don't like
having to explicitly do that for every table and NOT NULL column, since I
make pretty much routine use of RULES on VIEWS to make writeable views
the interface to my user application.

Out of curiosity, can you tell me what happens if you insert into the
view ('C',DEFAULT,DE FAULT)?
Richard Huxton

Same script, but with
INSERT INTO test_table VALUES ('A');
INSERT INTO test_table_v VALUES ('B');
INSERT INTO test_table_v VALUES ('C', DEFAULT, DEFAULT);

gives

field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
B | 2 | |
C | 3 | |
(3 rows)

-- so no change in behavior. I notice that field2, which was declared
type SERIAL, and so also has a DEFAULT, but one which calls the nextval
function rather than simply assigning a value, gets its default value
assigned in both the table insert and the view insert.

Because you don't specify field2 in your RULE.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Richard Huxton <de*@archonet.c om> writes:

On Friday 19 September 2003 09:00, bt****@seaworth ysys.com wrote:

Is this supposed to work that way? I would expect field3 and field4 to
have their respective column defaults assigned on the second INSERT (row
B), just like on the first INSERT (row A).

Hmm - well, you're explicitly telling it to insert VALUES (..., new.field3,
...) so if new.field3 is null then it *should* do that.

Exactly. The defaults attached to the underlying table determine what
gets added to an INSERT into the underlying table. In this case, since
the rule's INSERT specifies all the fields, there is no scope for those
defaults to apply.

What Berend actually wants is to attach column defaults to the *view*,
so that they apply to an INSERT mentioning the view. You can do this in
recent PG releases (7.3 for sure, not sure about 7.2) using ALTER TABLE
.... ADD DEFAULT.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Richard Huxton]

On Friday 19 September 2003 16:04, Tom Lane wrote:

Richard Huxton <de*@archonet.c om> writes:

On Friday 19 September 2003 09:00, bt****@seaworth ysys.com wrote:

Is this supposed to work that way? I would expect field3 and field4 to
have their respective column defaults assigned on the second INSERT (row
B), just like on the first INSERT (row A).

Hmm - well, you're explicitly telling it to insert VALUES (...,
new.field3, ...) so if new.field3 is null then it *should* do that.

Exactly. The defaults attached to the underlying table determine what
gets added to an INSERT into the underlying table. In this case, since
the rule's INSERT specifies all the fields, there is no scope for those
defaults to apply.

What Berend actually wants is to attach column defaults to the *view*,
so that they apply to an INSERT mentioning the view. You can do this in
recent PG releases (7.3 for sure, not sure about 7.2) using ALTER TABLE
... ADD DEFAULT.

Hmm - didn't know that. Useful.

Am I right in thinking that *in theory* we should be able to "inherit" the
defaults to the view automatically?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[btober]

> Richard Huxton <de*@archonet.c om> writes:

On Friday 19 September 2003 09:00, bt****@seaworth ysys.com wrote:

Is this supposed to work that way? I would expect field3 and field4
to have their respective column defaults assigned on the second
INSERT (row B), just like on the first INSERT (row A).

Hmm - well, you're explicitly telling it to insert VALUES (...,
new.field3, ...) so if new.field3 is null then it *should* do that.

Exactly. The defaults attached to the underlying table determine what
gets added to an INSERT into the underlying table. In this case, since
the rule's INSERT specifies all the fields, there is no scope for those
defaults to apply.

What Berend actually wants is to attach column defaults to the *view*,
so that they apply to an INSERT mentioning the view. You can do this
in recent PG releases (7.3 for sure, not sure about 7.2) using ALTER
TABLE ... ADD DEFAULT.

Now THAT's WAY cool! I can do ALTER TABLE ...ADD DEFAULT against views?

Sounds like exactly the ticket. Just tried it. It works.

That is SO sweet...the more I learn about pg the more excited I get.

~Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Richard Huxton <de*@archonet.c om> writes:

On Friday 19 September 2003 16:04, Tom Lane wrote:

What Berend actually wants is to attach column defaults to the *view*,
so that they apply to an INSERT mentioning the view. You can do this in
recent PG releases (7.3 for sure, not sure about 7.2) using ALTER TABLE
... ADD DEFAULT.

Am I right in thinking that *in theory* we should be able to "inherit" the
defaults to the view automatically?

[shrug] Maybe, in simple cases. I'm not convinced it'd be especially
useful. The defaults applicable to direct inserts into a table aren't
necessarily sensible for inserts via a view.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[btober]

On Friday 19 September 2003 16:04, Tom Lane wrote:

Richard Huxton <de*@archonet.c om> writes:

> On Friday 19 September 2003 09:00, bt****@seaworth ysys.com wrote:
>> Is this supposed to work that way? I would expect field3 and

field4 to have their respective column defaults assigned on the
second INSERT (row B), just like on the first INSERT (row A).

>
> Hmm - well, you're explicitly telling it to insert VALUES (...,

new.field3, ...) so if new.field3 is null then it *should* do that.

Exactly. The defaults attached to the underlying table determine
what gets added to an INSERT into the underlying table. In this
case, since the rule's INSERT specifies all the fields, there is no
scope for those defaults to apply.

What Berend actually wants is to attach column defaults to the
*view*, so that they apply to an INSERT mentioning the view. You can
do this in recent PG releases (7.3 for sure, not sure about 7.2)
using ALTER TABLE ... ADD DEFAULT.

Hmm - didn't know that. Useful.

Am I right in thinking that *in theory* we should be able to "inherit"
the defaults to the view automatically?

I think that what you describe here as inheriting the defaults is the
behavior I mistakenly expected as normal. But being able to assigne
defaults to view columns is pretty darn cool.

~Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend