Insert statement and DEFAULT keyword

[Nicolas Caniart]

Hi !

I've been looking into the documentation but could not find it.

What is the proper way to insert a DEFAULT keywork in the tuples of the VALUES clause in an INSERT statement ? As in

CREATE TABLE number (

    i INTEGER,

    letters STRING,

    roman STRING DEFAULT '',

) ;

INSERT INTO number (c1, c2, c3) VALUES (1, 'one', 'I'), (1000000, 'one million', DEFAULT) ; -- NOTE THE DEFAULT HERE

(yes this example is silly)

The best I came up with, is using the `literal_column('DEFAULT', type_=...)` construct.

I wondered if there is a better way, cause I was worried about the keyword possibly getting quoted at some point, though I am not 100% sure that could happen.

Thanks in advance !

Regards,

Nicolas.

[Mike Bayer]

On Tue, Apr 14, 2020, at 10:41 AM, Nicolas Caniart wrote:

Hi !

I've been looking into the documentation but could not find it.

What is the proper way to insert a DEFAULT keywork in the tuples of the VALUES clause in an INSERT statement ? As in

CREATE TABLE number (

    i INTEGER,

    letters STRING,

    roman STRING DEFAULT '',

) ;

INSERT INTO number (c1, c2, c3) VALUES (1, 'one', 'I'), (1000000, 'one million', DEFAULT) ; -- NOTE THE DEFAULT HERE

I'm not familiar with this keyword but to my knowledge there is no need for it on any backend I'm familiar with, you instead omit the "roman" column from the insert construct itself by only specifying values for the "i" and "letters" columns.    The "roman" column will not be present and the SQL side default will fire off.

If you can share the reason that this special DEFAULT keyword is otherwise needed as well as what kind of database this is feel free, literal_column() would be the only way to go.

(yes this example is silly)

The best I came up with, is using the `literal_column('DEFAULT', type_=...)` construct.

I wondered if there is a better way, cause I was worried about the keyword possibly getting quoted at some point, though I am not 100% sure that could happen.

Thanks in advance !

Regards,

Nicolas.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9c133c97-a5c6-4863-8f40-d8174949f4d7%40googlegroups.com.

[Nicolas Caniart]

As you may have guessed the DEFAULT keyword is used to "even" the size of the tuples in the VALUES clause and explicitly asks the RDBMS to replace itself with the currently defined default value for the corresponding column.

The RDBMS in question is Postgres (>=11).

The context is we want to insert, in batches, data that comes for a message queue. Data comes in as (flat) JSON documents, but where some keys may be omitted. We'd like to insert the batches without the need to know the server defaults. We don't use the ORM layer, only the core layer (though I don't think that would make any difference).

I attached an example that shows why using the DEFAULT keyword seems useful: it avoids errors or data losses. A short comment explains what's wrong at the top of each case. The example, to be run successfully, assumes a Postgres server is running, accessible through its unix socket, and the current user as access to a default database (generally one which name matches is login name) and can create a table in the public schema. You can change the server address if need be (see l. 48), change the schema (l. 42) or table name (l. 36).

The important thing in the previous example was that we have tuples of different sizes: a 3-tuple and a 2-tuple (when DEFAULT is omitted). Something that, if fed as is to your SQL server, it will probably choke on: (1,'one', 'I') and (1000000, 'one million'). If I do what you suggest (omit the roman column) then I cannot pass an explicit value for that column when I have one.

A detail I forgot in the previous message is that the `roman` column in the `numbers` table is not nullable.

CREATE TABLE number (

    i INTEGER,

    letters STRING,

    roman STRING DEFAULT 'inexpressible' NOT NULL,

) ;

I mention this because I found old posts that mention in some case tuples may be complete with NULL(s), but that is not what I observed and would not work anyway in our context. You might argue why not drop the NOT NULL and make NULL equivalent to 'inexpressible'. That would make perfect sense, I a perfect world. But assume the database is ages old, hence has lots of quirks I cannot get rid of (yet).

Of course, I may be missing something, probably obvious, that would explain why nobody asked about this before. Hope it is a bit clearer.

Regards,

Nicolas

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mike Bayer]

oh, you're using insert.values() with multivalues.   Sure, use a literal_column() for that, this is not a normal SQL thing.    it won't scale to huge numbers because you will overflow the query buffer, unless you break them into batches.

I might prefer to use the psycopg2 batch mode helpers instead which makes the whole "VALUES" thing and the batching transparent, I would probably just pass the known defaults straight in assuming they are constants.  https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-fast-execution-helpers

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/93a9c888-976c-4fcf-a739-dffbf62172bd%40googlegroups.com.

Attachments:

• bulk_insert_default.py