Re: insert DEFAULT value

On Thu, 4 Mar 2021, 06:43 Hans Ginzel, <hans(at)matfyz(dot)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