Re: How to insert either a value or the column default?

On 08/23/2014 11:10 AM, W. Matthew Wilson wrote:
> 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?

Some playing around with indicates the DEFAULT keyword only has context
when directly entered in the VALUES portion of the INSERT statement.
Trying to use it COALESCE or in CASE fails.

>
> 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.

In the end ORMs build SQL queries from strings also.

When I have done this I build the column/field string and then the
VALUES string using %s or %(some_field)s. Assuming you are using
psycopg2 and passing the parameters using a list or dictionary
respectively then the data will be properly escaped.

>
> Any advice is welcome. Thanks in advance!
>
>
> Matt
>
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com