Re: Using Insert - Default in a condition expression ??

On Tue, Sep 29, 2009 at 04:04:46AM -0700, Postgres User wrote:
> >> > I'm trying to write an INSERT INTO statement that will use a DEFAULT
> >> > value when an input parameter is null.

[ workaround given ]

> Again, this approach works for a simple example. But for a larger
> function with any number of input params and multiple columns with
> default values, it's not practical for an ORM code generator.

Just to reiterate what Richard said; this is really the duty of the ORM.

If you're telling the database to insert a NULL then that's what the
database should do. If you tell it to insert a DEFAULT that that's what
it should do. The fact that your ORM doesn't distinguish between the
two is a bug in your ORM and should really be fixed there.

One of PG's design mantras is to do (exactly) what the user says or tell
them why it's not possible. Silently changing NULLs into DEFAULT values
doesn't seem like correct behavior and will generally come back and bite
you later on---for example, what if the user really does want to insert
a NULL value into *any* column that has a non-NULL DEFAULT value.

--
Sam http://samason.me.uk/