How to insert either a value or the column default?

["W. Matthew Wilson" <matt@xxxxxxxxxx>]

I have a table that looks sort of like this:

    create table tasks
    (
        task_id serial primary key,
        title text,
        status text not null default 'planned'
    );

In python, I have a function like this:

    def insert_task(title, status=None):
        ....

and when status is passed in, I want to run a SQL insert statement like this:

    insert into tasks
    (title, status)
    values
    (%s, %s)

but when status is not passed in, I want to run this SQL insert instead:

    insert into tasks
    (title, status)
    values
    (%s, default)

I know how to pick the query with an if-clause in python, but I wish
it were possible to do something like this:

    insert into tasks
    (title, status)
    values
    (%s, coalesce(%s, default))

I have tried different variations, but I keep getting syntax errors.
Is there any way to do say:

    "if the value is not null, insert the value.  Otherwise, insert
the default value for this column"

entirely in SQL?

When there is just one optional column, it is not a big deal to use an
if-clause in python.  But there are numerous optional columns.

I know I could build up lists of strings in python but I'm hoping
there's a simpler way to do this in the query.

But I have a hard time already getting other programmers to understand
SQL injection attacks and if they see me building up SQL queries from
strings, even though there's no risk of a SQL injection in this
scenario, I still don't want to break my "no string interpolation"
rule of thumb unless I absolutely have to.

And I know I could switch to some gigantic library like SQLAlchemy,
but I really don't want to.

Any advice is welcome.  Thanks in advance!


Matt









-- 
W. Matthew Wilson
matt@xxxxxxxxxx
http://tplus1.com


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general