"Eric B. Ridge" <ebr@tcdi.com> writes:
> INSERT INTO foo (id, title, type) VALUES (<default>, 'whatever',
> <default>);
> Is there no keyword for getting the DEFAULT value of a column when
> doing an INSERT?
Not sure how far back this goes, but in 7.3 you can say DEFAULT:
INSERT INTO foo (id, title, type) VALUES (default, 'whatever',
default);
which is per SQL spec.
> Alternatively, it would be really sweet if ON INSERT RULEs would
> provide the DEFAULT values in NEW for those fields that weren't
> specified in the triggering INSERT statement.
Again, I don't recall when we implemented this, but you can attach
column default values to views in 7.3, and maybe before. In your
example:
> CREATE VIEW foo_view AS SELECT * FROM foo;
> CREATE RULE foo_rule ON INSERT TO foo_view DO INSTEAD (
> INSERT INTO some_other_table (id, title, type) values (NEW.id,
> NEW.title, NEW.type)
> );
> INSERT INTO foo_view (title) values ('whatever');
the unspecified columns will be driven by any column defaults attached
to foo_view (not foo). You say, eg,
alter table foo_view alter column id set default nextval('seq_foo_id');
and away you go.
regards, tom lane