Subject: insert DEFAULT value

From: Hans Ginzel <[email-masked]>
Date: Wed, 3 Mar 2021 22:51:37 +0100

How to insert DEFAULT value, please?

import psycopg2
from psycopg2.sql import DEFAULT	# https://www.postgresql-archive.org/Inserting-default-values-into-execute-values-td6130148.html

db = psycopg2.connect(host='host', dbname='db')
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS test_default")
cursor.execute("CREATE TABLE test_default(i int NOT NULL DEFAULT 1)")
cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))

cursor.execute("DROP TABLE IF EXISTS test_default")
cursor.execute("CREATE TABLE test_default(j jsonb NOT NULL DEFAULT '{}'::jsonb)")
cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))

For both cases I get
psycopg2.ProgrammingError: can't adapt type 'SQL'

Thank you in advance,
HG

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 3 Mar 2021 15:23:28 -0800

On 3/3/21 1:51 PM, Hans Ginzel wrote:
> from psycopg2.sql import DEFAULT    # 
> https://www.postgresql-archive.org/Inserting-default-values-into-execute-values-td6130148.html 
> 
> 

You need to do something like:

cur.execute(sql.SQL("INSERT INTO test_default VALUES ({})").format(DEFAULT))

-- 
Adrian Klaver
[email-masked]

---

From: Hans Ginzel <[email-masked]>
Date: Thu, 4 Mar 2021 06:43:28 +0100

>cur.execute(sql.SQL("INSERT INTO test_default VALUES ({})").format(DEFAULT))

Formating values into SQL statement is done by the execute(), isn't it?

I like the solution from

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.
   """
   def __conform__(self, proto):
     if proto is psycopg2.extensions.ISQLQuote:
       return self
   def getquoted(self):
     return 'DEFAULT'

DEFAULT = Default()

Could this be added to the psycopg2 code, please?

H.

---

From: Daniele Varrazzo <[email-masked]>
Date: Thu, 4 Mar 2021 11:33:45 +0100

> 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.

> 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