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

How to insert either a value or the column default?

From

"W. Matthew Wilson"

Date:

23 August 2014, 21:10:43

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@tplus1.com
http://tplus1.com

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

From

John McKown

Date:

23 August 2014, 22:41:19

Would you mind a non-python solution? I think the following will work for you. It uses an INSERT trigger on the table "tasks". The only minus is keeping the default value in both the table definition and the trigger. Of course, it is not really needed in the definition of the row value in the table due to the INSERT trigger supplying it anyway. For multiple possible fields, just have another if sequence.

drop table if exists tasks cascade; -- for redefinition

drop function if exists tasks_status(); --for redefinition

create table tasks (

task_id serial primary key,

title text,

status text not null default 'planned'

);

-- name the function whatever you want.

create function tasks_status() returns trigger as $task_status$

begin

-- see if status is NULL and replace if so

if NEW.status is NULL then

NEW.status := 'planned';

end if;

-- if NEW.other is NULL then

-- NEW.Other := <default value>

-- end if;

-- repeat the if to end if some more it required.

return NEW;

end;

$task_status$ LANGUAGE plpgsql;

-- name the trigger whatever you want. I made the same

-- as the procedure just to keep them "linked" in my

-- mind. Make sure you change the procedure name

-- referenced if you change the function name.

create trigger tasks_status before insert or update on tasks

for each row execute procedure tasks_status();

-- just some example insert commands to

-- test the trigger. took me 10 tries to get

-- it typed in correctly. Stupid fingers! [grin]

insert into tasks(title,status) values('one','active');

insert into tasks(title) values('two');

insert into tasks(title,status) values('three',NULL);

On Sat, Aug 23, 2014 at 1:10 PM, W. Matthew Wilson <matt@tplus1.com> 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?

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@tplus1.com
http://tplus1.com

There is nothing more pleasant than traveling and meeting new people!

Genghis Khan

Maranatha! <><
John McKown

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

From

Adrian Klaver

Date:

24 August 2014, 19:35:59

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.klaver@aklaver.com

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

From

Daniele Varrazzo

Date:

24 August 2014, 21:50:38

On Sat, Aug 23, 2014 at 7:10 PM, W. Matthew Wilson <matt@tplus1.com> 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)

You can "easily" do that in psycopg with:

    class Default(object):
        def __conform__(self, proto):
            if proto is psycopg2.extensions.ISQLQuote:
                return self
        def getquoted(self):
            return 'DEFAULT'

    DEFAULT = Default()

    >>> print cur.mogrify('insert into place values (%s, %s)',
['adsf', DEFAULT])
    insert into place values ('adsf', DEFAULT)

You can find more details at
http://initd.org/psycopg/docs/advanced.html#adapting-new-python-types-to-sql-syntax

It should be added to the library (it was first discussed in 2003...),
but it's one of these things that will stop working when psycopg will
start using the "extended query protocol" (together with other nifty
features such as string literals for table/columns names) so in my
mind it can only be included when psycopg will be able to do both
client-side parameter interpolation and server-side arguments passing,
and when the distinction between the two strategies will be clear
(this is planned for a future psycopg3 but there is no timeline for it
yet).

-- Daniele

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

From

Adrian Klaver

Date:

24 August 2014, 22:12:33

On 08/24/2014 11:50 AM, Daniele Varrazzo wrote:
> On Sat, Aug 23, 2014 at 7:10 PM, W. Matthew Wilson <matt@tplus1.com> 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)
>
> You can "easily" do that in psycopg with:
>
>      class Default(object):
>          def __conform__(self, proto):
>              if proto is psycopg2.extensions.ISQLQuote:
>                  return self
>          def getquoted(self):
>              return 'DEFAULT'
>
>      DEFAULT = Default()
>
>      >>> print cur.mogrify('insert into place values (%s, %s)',
> ['adsf', DEFAULT])
>      insert into place values ('adsf', DEFAULT)
>

Well that is cool. So you could do:

status = None

In [17]: cur.execute("insert into tasks(title, status) values(%s, %s)",
["first", status or DEFAULT])

In [18]: con.commit()


test=> select * from tasks;
  task_id | title | status
---------+-------+---------
        1 | first | planned


> yet).
>
> -- Daniele
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

"W. Matthew Wilson"

Date:

25 August 2014, 16:32:22

On Sun, Aug 24, 2014 at 2:50 PM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> It should be added to the library (it was first discussed in 2003...),
> but it's one of these things that will stop working when psycopg will
> start using the "extended query protocol" (together with other nifty
> features such as string literals for table/columns names) so in my
> mind it can only be included when psycopg will be able to do both
> client-side parameter interpolation and server-side arguments passing,
> and when the distinction between the two strategies will be clear
> (this is planned for a future psycopg3 but there is no timeline for it
> yet).

First of all, thanks for showing this trick!

But I am confused.  Will this trick stop working in a future version
of psycopg2?  Should I avoid using it?

Thanks again!

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

From

Adrian Klaver

Date:

25 August 2014, 17:26:58

On 08/25/2014 06:32 AM, W. Matthew Wilson wrote:
> On Sun, Aug 24, 2014 at 2:50 PM, Daniele Varrazzo
> <daniele.varrazzo@gmail.com> wrote:
>> It should be added to the library (it was first discussed in 2003...),
>> but it's one of these things that will stop working when psycopg will
>> start using the "extended query protocol" (together with other nifty
>> features such as string literals for table/columns names) so in my
>> mind it can only be included when psycopg will be able to do both
>> client-side parameter interpolation and server-side arguments passing,
>> and when the distinction between the two strategies will be clear
>> (this is planned for a future psycopg3 but there is no timeline for it
>> yet).
>
> First of all, thanks for showing this trick!
>
> But I am confused.  Will this trick stop working in a future version
> of psycopg2?  Should I avoid using it?

Here is the email laying out the issues:

http://www.postgresql.org/message-id/AANLkTi=ym3SCQKCQBtp8RJHUswwAPOpjXYKTXS=aHWzp@mail.gmail.com



>
> Thanks again!
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Daniele Varrazzo

Date:

25 August 2014, 18:36:26

On Mon, Aug 25, 2014 at 3:26 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 08/25/2014 06:32 AM, W. Matthew Wilson wrote:

>> First of all, thanks for showing this trick!
>>
>> But I am confused.  Will this trick stop working in a future version
>> of psycopg2?  Should I avoid using it?
>
>
> Here is the email laying out the issues:
>
> http://www.postgresql.org/message-id/AANLkTi=ym3SCQKCQBtp8RJHUswwAPOpjXYKTXS=aHWzp@mail.gmail.com

That's correct, thank you Adrian.

Matthew: no, this will correctly work in all the future psycopg2
versions. Even if we started supporting a new protocol, such as the
ISQLParam referred in the mentioned email, it won't be the default in
psycopg2 and it should be enabled on purpose.

The new protocol should be the default in this mythical psycopg3
instead. If something can be imported as psycopg2 it will support the
ISQLQuote protocol by default, hence the Default object as implemented
in this thread will work.

-- Daniele