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*******@postgresql.org so that your
message can get through to the mailing list cleanly 8 2297
On Friday 19 September 2003 09:00, bt****@seaworthysys.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,DEFAULT)?
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html On Friday 19 September 2003 09:00, bt****@seaworthysys.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.field3, 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,DEFAULT)? 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
On Friday 19 September 2003 13:36, bt****@seaworthysys.com wrote: On Friday 19 September 2003 09:00, bt****@seaworthysys.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.field3, 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,DEFAULT)? 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
Richard Huxton <de*@archonet.com> writes: On Friday 19 September 2003 09:00, bt****@seaworthysys.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 YourEmailAddressHere" to ma*******@postgresql.org)
On Friday 19 September 2003 16:04, Tom Lane wrote: Richard Huxton <de*@archonet.com> writes: On Friday 19 September 2003 09:00, bt****@seaworthysys.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
> Richard Huxton <de*@archonet.com> writes: On Friday 19 September 2003 09:00, bt****@seaworthysys.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
Richard Huxton <de*@archonet.com> 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*******@postgresql.org On Friday 19 September 2003 16:04, Tom Lane wrote: Richard Huxton <de*@archonet.com> writes: > On Friday 19 September 2003 09:00, bt****@seaworthysys.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: serge |
last post by:
I am doing a little research on Google about this topic and I ran into
this thread:
...
|
by: Jane |
last post by:
Does any one know why this statement is failing?
db2 => ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51)
DB21034E The command was processed as an SQL statement because it was...
|
by: Robert Stearns |
last post by:
Either I missed something, or ALTER TABLE does not have this
capability. Is there any way of doing it except
DROPping all constraints which mention this table,
EXPORTing the data,
DROPping the...
|
by: Renato Cramer |
last post by:
Hello All,
There is data of several enterprises (ours clients) in a single database.
All tables have a column on primary key what identify the enterprise called
id_enterprise.
My objective is...
|
by: David Thielen |
last post by:
Hi;
I am sure I am missing something here but I cannot figure it out. Below I
have a program and I cannot figure out why the xpath selects that throw an
exception fail. From what I know they...
|
by: surya |
last post by:
hello sir,
i have a table emp ,it has three fields one is empno int ,second is
ename varchar(20).
and last is salary ,
emp
empno ename salary
----------- ------------...
|
by: axelsino |
last post by:
I know this has been asked before and yes, I have read the section in
the documentation about it.
But, my question is: If I have setup mysql with strict_trans_tables,
will MySQL allow "null"...
|
by: Sarita |
last post by:
Hello,
this might sound stupid, but I got a really nice homepage template
which unfortunately is a 3-Column Fixed Width CSS format. Now I don't
have any content for the right column and would...
|
by: Shestine |
last post by:
I am trying to add a column to a current table, with data in it. I am only learning, and i have no idea how to change this to make it work. Here is the script I have right now it, but what it does is...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |