Re: How to insert either a value or the column default? - Mailing list pgsql-general

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