Subject: Inserting default values into execute_values

From: Stephen Lagree <[email-masked]>
Date: Tue, 31 Mar 2020 15:27:05 -0700

Hello,

I am trying to insert into a table to generate sequential ids.  Is there a
way to do this repeatedly using execute_values if there is only one column
and it is auto incremented?
It seems the execute_values requires at least one non-default value.

I am trying to do this:
    query = "INSERT INTO MYTABLE (id) VALUES (DEFAULT) RETURNING id;"
    execute_values(cursor, query, args_list, template=None, page_size=100,
fetch=True)

If I don't use a %s argument and just put dummy values in the arglist, I
get error
E           ValueError: the query doesn't contain any '%s' placeholder
I understand why this doesn't work because it can't extract the placeholder
and replicate values there.

If I change DEFAULT to %s and try to use blank tuples I get this
E           psycopg2.errors.SyntaxError: syntax error at or near ")"
E           LINE 1: INSERT INTO MYTABLE (id) VALUES (),(),() RETURNING id;

If I use "DEFAULT" as a string it tries to insert a string into an int
column, not use the DEFAULT value.  Is there a way to insert the default
value here?  I don't see anything like this in the documentation.

My table looks like this:
"CREATE TABLE MYTABLE (id SERIAL PRIMARY KEY)"

Thanks,
Steve

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 31 Mar 2020 15:57:31 -0700

On 3/31/20 3:27 PM, Stephen Lagree wrote:
> Hello,
> 
> I am trying to insert into a table to generate sequential ids.  Is there 
> a way to do this repeatedly using execute_values if there is only one 
> column and it is auto incremented?
>      execute_values(cursor, query, args_list, template=None, 
> page_size=100, fetch=True)
> 
> I understand why this doesn't work because it can't extract the 
> placeholder and replicate values there.
> 

A solution from Daniele Varrazzo.  I can't find the mailing list post 
where it appeared, just where I use it in code:

Given a file:

utilities/psycopg_helpers.py

"""Psycopg2 helper code.

Code for extending psycopg2.
"""

import psycopg2

class Default(object):
     """Set up DEFAULT value for a field.

     When doing INSERT or UPDATE in Postgres one can use DEFAULT/default 
    as the value to have the server use the default set on the field. 
The below allows
     for doing that.
     """
     def __conform__(self, proto):
         if proto is psycopg2.extensions.ISQLQuote:
             return self

     def getquoted(self):
         return 'DEFAULT'

DEFAULT = Default()

Then import it:

from .utilities.psycopg_helpers import DEFAULT

and use DEFAULT where you want a SQL DEFAULT.

-- 
Adrian Klaver
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 31 Mar 2020 16:08:29 -0700

On 3/31/20 3:57 PM, Adrian Klaver wrote:
>> I am trying to insert into a table to generate sequential ids.  Is 
>> there a way to do this repeatedly using execute_values if there is 
>> only one column and it is auto incremented?
>> If I don't use a %s argument and just put dummy values in the arglist, 
>> I get error
>> column, not use the DEFAULT value.  Is there a way to insert the 
>> default value here?  I don't see anything like this in the documentation.
>>

I was searching the wrong list it is in the pgsql-general list:

https://www.postgresql.org/message-id/CA%2Bmi_8ZQx-vMm6PMAw72a0sRATEh3RBXu5rwHHhNNpQk0YHwQg%40mail.gmail.com

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 1 Apr 2020 15:16:58 +1300

> >> only one column and it is auto incremented?

The point of execute_values is to convert a sequence of records into a
VALUES thing (that's what the placeholder is for) and shoot it to the
db in one go. I think your task is much simpler than that.

In order to do what you want to do you use execute_batch and use a
list of empty tuples for instance;

    psycopg2.extras.execute_batch(cur, "insert into testins (id)
values (default)", [() for i in range(10)])

but I think this is still silly: you are still sending a lot of
strings from client to serve which do very little.

You can easily do the same loop entirely in the database, executing a
statement such as:

    do $$
    declare i int;
    begin
        for i in select * from generate_series(1, 10)
        loop
            insert into testins (id) values (default);
        end loop;
    end
    $$ language plpgsql;

but this is still means doing n separate inserts. Even faster would be
just not rely on the DEFAULT literal, if you know the table you are
inserting into or you don't mind introspecting the schema:

    insert into testins (id) select nextval('testins_id_seq') from
generate_series(1, 10);

> > where it appeared, just where I use it in code:

Thank you for fishing that out! But I think since the introduction of
the 'psycopg2.sql' module the correct way to do that is to use
something like 'sql.SQL("DEFAULT")' to compose into a query.

Cheers,

-- Daniele

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 1 Apr 2020 13:03:38 -0700

> something like 'sql.SQL("DEFAULT")' to compose into a query.

Thanks, still wrapping my head around psycopg2.sql.

A simple example:

test=# \d t2
                                  Table "public.t2"
  Column |       Type        | Collation | Nullable |            Default 

--------+-------------------+-----------+----------+--------------------------------
  id     | integer           |           | not null | 
nextval('t2_id_seq'::regclass)
  name   | character varying |           |          |
Indexes:
     "t2_pkey" PRIMARY KEY, btree (id)

import psycopg2
from psycopg2 import sql

con = psycopg2.connect("dbname=test host=localhost user=aklaver")

q1 = sql.SQL("insert into t2  values 
({})").format(sql.SQL(",").join([sql.SQL("DEFAULT"), 
sql.Literal('test2')]))

print(q1.as_string(con)) 

insert into t2  values (DEFAULT,E'test2')

cur.execute(q1)

test=# select * from t2;
  id | name
----+-------
   1 | test
   2 | test2

---

From: Stephen Lagree <[email-masked]>
Date: Wed, 1 Apr 2020 13:31:25 -0700

Thanks Daniele and Adrian, your answers were really helpful!

Daniele, you are right, it is a waste sending long strings when I am just
trying to generate entries in the sequence.
I do want to do it in one shot so your generate_series suggestion should be
great
    insert into testins (id) select nextval('testins_id_seq') from
generate_series(1, 10);

However, I was playing around with the sql.Default and Adrian's Default
class and couldn't get them to work with execute_values.  I know in my case
it might not make sense to use a Default literal if that is all that is
being added, but it might make sense for a query that sometimes is used for
inserting DEFAULT and sometimes to insert a value.

            query2 = "INSERT INTO MYTABLE (id) VALUES %s RETURNING id;"
            args_list = [sql.DEFAULT, sql.DEFAULT]
            execute_values(cursor, query2, args_list,
                           template=None, page_size=100, fetch=True)

There is a TypeError in execute_values for both Adrian's Default and
sql.Default:

            for page in _paginate(argslist, page_size=page_size):
                if template is None:
    >               template = b'(' + b','.join([b'%s'] * len(page[0])) +
b')'
    E               TypeError: object of type 'SQL' has no len()

    ../../.con
da/envs/stbase/lib/python3.7/site-packages/psycopg2/extras.py:1275:
TypeError

I added a len and slicing function to Adrian's default class and tried it,
but it then had an error with the mogrify line in execute values.  I tried
a few variations of templates with and without parentheses and that didn't
work either.

-Steve

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 1 Apr 2020 14:04:37 -0700

On 4/1/20 1:31 PM, Stephen Lagree wrote:
> Daniele, you are right, it is a waste sending long strings when I am 
> just trying to generate entries in the sequence.
> I do want to do it in one shot so your generate_series suggestion should 
> be great
> class and couldn't get them to work with execute_values.  I know in my 
> case it might not make sense to use a Default literal if that is all 
> that is being added, but it might make sense for a query that sometimes 
> is used for inserting DEFAULT and sometimes to insert a value.
> 
>      >               template = b'(' + b','.join([b'%s'] * len(page[0])) 
> + b')'
> I added a len and slicing function to Adrian's default class and tried 
> it, but it then had an error with the mogrify line in execute values.  I 
> tried a few variations of templates with and without parentheses and 
> that didn't work either.

The DEFAULT and sql.SQL("DEFAULT") both return objects that do not play 
well with the template as you found out.

The simplest way I found is to do:

query2 = "INSERT INTO t2 (id, name) VALUES %s RETURNING id;"

execute_values(cur, query2, args_list, template="(DEFAULT, DEFAULT)", 
page_size=100, fetch=True) 

[(3,), (4,)]

test=# alter table t2 alter COLUMN name set default 'name';
ALTER TABLE
test=# select * from t2;
  id | name
----+-------
   1 | test
   2 | test2
(2 rows)

test=# select * from t2;
  id | name
----+-------
   1 | test
   2 | test2
   3 | name
   4 | name
(4 rows)

> 
> -Steve
> 
> On Wed, Apr 1, 2020 at 1:03 PM Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>      > On Wed, 1 Apr 2020 at 12:08, Adrian Klaver
>     <[email-masked] <mailto:[email-masked]>> wrote:
>      >
>     [email-masked] <mailto:[email-masked]>
>

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 1 Apr 2020 14:15:49 -0700

On 4/1/20 2:04 PM, Adrian Klaver wrote:
>> I do want to do it in one shot so your generate_series suggestion 
>> should be great
>> However, I was playing around with the sql.Default and Adrian's 
>> Default class and couldn't get them to work with execute_values.  I 
>> know in my case it might not make sense to use a Default literal if 
>> that is all that is being added, but it might make sense for a query 
>> that sometimes is used for inserting DEFAULT and sometimes to insert a 
>> value.
>>
>>      >               template = b'(' + b','.join([b'%s'] * 
>> len(page[0])) + b')'
>> it, but it then had an error with the mogrify line in execute values.  
>> I tried a few variations of templates with and without parentheses and 
>> that didn't work either.
> 

Not concentrating, the above is not correct for the DEFAULT case:

class Default(object):

     def getquoted(self):
         return 'DEFAULT'

DEFAULT = Default()

args_list = [(DEFAULT, DEFAULT)]

execute_values(cur, query2, args_list, template=None, page_size=100, 
fetch=True) 

[(6,)]

select * from t2;
  id | name
----+-------
   1 | test
   2 | test2
   3 | name
   4 | name
   5 | name
   6 | name

What we both forgot is that args_list needs to a sequence of sequences.