MIME-Version: 1.0
References: <20210303215137.GI11758@artax.karlin.mff.cuni.cz>
 <05f8e563-162a-3a86-d939-7fb7f03811e6@aklaver.com>
 <20210304054327.GL11758@artax.karlin.mff.cuni.cz>
In-Reply-To: <20210304054327.GL11758@artax.karlin.mff.cuni.cz>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Thu, 4 Mar 2021 11:33:45 +0100
Message-ID: 
 <CA+mi_8Z-LK0xmcfSmaARfg_kiw0s37-TiOR4ZJqW9rWVLTqtrg@mail.gmail.com>
Subject: Re: insert DEFAULT value
To: Hans Ginzel <hans@matfyz.cz>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, psycopg@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003f78b305bcb381a4"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bmi_8Z-LK0xmcfSmaARfg_kiw0s37-TiOR4ZJqW9rWVLTqtrg%40mail.gmail.com>
Precedence: bulk

--0000000000003f78b305bcb381a4
Content-Type: text/plain; charset="UTF-8"

On Thu, 4 Mar 2021, 06:43 Hans Ginzel, <hans@matfyz.cz> wrote:

> On Wed, Mar 03, 2021 at 03:23:28PM -0800, Adrian Klaver wrote:
> >On 3/3/21 1:51 PM, Hans Ginzel wrote:
> >You need to do something like:
> >cur.execute(sql.SQL("INSERT INTO test_default VALUES
> ({})").format(DEFAULT))
>
> Formating values into SQL statement is done by the execute(), isn't it?
>

It's done on the client. It's done by execute(), yes, by calling
".as_string(conn)" on the resulting object, which is something exposed by
the API and which you can do too in your program.


cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))
>
> Could this be added to the psycopg2 code, please?
>

Nope, not a chance. I thought it was a good idea when I proposed it, which
was not in April 2020, when my message was fished from the interwebz
memories, but I think I wrote it around 2006? Anyway I would answer my then
self that it is not a good idea. Parameter passing is for parameters,
DEFAULT is a SQL construct: it wouldn't work when the adapter evolves into
sending query and params separately (which is happening in psycopg3).

So it won't land in the adapter, no, and the correct way of doing that job
is to use explicit client-side binding through the psycopg2.sql facilities,
passing a 'sql.Literal(value)', or a 'sql.SQL("DEFAULT")' to override it,
to a '{}' placeholder, or to pass a 'sql.Placeholder()' instead of
DEFAULT... many ways to skin a client-side cat.

You are free to use my old recipe but it won't be added in psycopg2, which
has since grown a better way to do the same task. :)

Cheers

-- Daniele

--0000000000003f78b305bcb381a4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Thu, 4 Mar 2021, 06:43 Hans Ginzel, &lt;<a href=3D"=
mailto:hans@matfyz.cz">hans@matfyz.cz</a>&gt; wrote:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;=
padding-left:1ex">On Wed, Mar 03, 2021 at 03:23:28PM -0800, Adrian Klaver w=
rote:<br>
&gt;On 3/3/21 1:51 PM, Hans Ginzel wrote:<br>
&gt;You need to do something like:<br>
&gt;cur.execute(sql.SQL(&quot;INSERT INTO test_default VALUES ({})&quot;).f=
ormat(DEFAULT))<br>
<br>
Formating values into SQL statement is done by the execute(), isn&#39;t it?=
<br></blockquote></div></div><div dir=3D"auto"><br></div><div dir=3D"auto">=
It&#39;s done on the client. It&#39;s done by execute(), yes, by calling &q=
uot;.as_string(conn)&quot; on the resulting object, which is something expo=
sed by the API and which you can do too in your program.</div><div dir=3D"a=
uto"><br></div><div dir=3D"auto"><br></div><div dir=3D"auto"><div class=3D"=
gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;b=
order-left:1px #ccc solid;padding-left:1ex">cursor.execute(&quot;INSERT INT=
O test_default VALUES (%s)&quot;, (DEFAULT,))<br>
<br>
Could this be added to the psycopg2 code, please?<br></blockquote></div></d=
iv><div dir=3D"auto"><br></div><div dir=3D"auto">Nope, not a chance. I thou=
ght it was a good idea when I proposed it, which was not in April 2020, whe=
n my message was fished from the interwebz memories, but I think I wrote it=
 around 2006? Anyway I would answer my then self that it is not a good idea=
. Parameter passing is for parameters, DEFAULT is a SQL construct: it would=
n&#39;t work when the adapter evolves into sending query and params separat=
ely (which is happening in psycopg3).</div><div dir=3D"auto"><br></div><div=
 dir=3D"auto">So it won&#39;t land in the adapter, no, and the correct way =
of doing that job is to use explicit client-side binding through the psycop=
g2.sql facilities, passing a &#39;sql.Literal(value)&#39;, or a &#39;sql.SQ=
L(&quot;DEFAULT&quot;)&#39; to override it, to a &#39;{}&#39; placeholder, =
or to pass a &#39;sql.Placeholder()&#39; instead of DEFAULT... many ways to=
 skin a client-side cat.</div><div dir=3D"auto"><br></div><div dir=3D"auto"=
>You are free to use my old recipe but it won&#39;t be added in psycopg2, w=
hich has since grown a better way to do the same task. :)</div><div dir=3D"=
auto"><br></div><div dir=3D"auto">Cheers=C2=A0</div><div dir=3D"auto"><br><=
/div><div dir=3D"auto">-- Daniele</div><div dir=3D"auto"><br></div></div>

--0000000000003f78b305bcb381a4--