Subject: Re: Inserting default values into execute_values
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Stephen Lagree <stephen.lagree@gmail.com>, psycopg@postgresql.org
References: 
 <CADkZaxXqU+c6_GnW_L0SOgc63iYHomm0RVSpb_+Kww5scVyA4w@mail.gmail.com>
 <6db34cfa-c531-18ff-4368-bea8c171f6db@aklaver.com>
Message-ID: <3cdc3522-f09a-529a-e329-b50418fb4172@aklaver.com>
Date: Tue, 31 Mar 2020 16:08:29 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.6.0
MIME-Version: 1.0
In-Reply-To: <6db34cfa-c531-18ff-4368-bea8c171f6db@aklaver.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 3/31/20 3:57 PM, Adrian Klaver wrote:
> 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?
>> 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)"
> 
> 
> A solution from Daniele Varrazzo.  I can't find the mailing list post 
> where it appeared, just where I use it in code:
> 

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com