<p dir="auto"><strong>Migrated issue, originally created by jan.budinsky (<a href="https://github.com/janbudinsky">@janbudinsky</a>)</strong></p>
<p dir="auto">Default column value is not overridden by <code class="notranslate">NULL</code> in <code class="notranslate">Declarative API</code> as it should be.</p>
<p dir="auto">If a column has a non-null default value that is overridden by another non-null value, everything works as expected - the specified value is always inserted as the value of the column instead of the default one.</p>
<p dir="auto">This however does not seem to be the case when overriding non-null default value with <code class="notranslate">NULL</code>. If a matching row (via primary key) has been already created (<code class="notranslate">merge</code> does <code class="notranslate">UPDATE</code>), the column will have <code class="notranslate">NULL</code> filled as its value, as expected. But if a matching row hasn't been created yet (<code class="notranslate">merge</code> does <code class="notranslate">INSERT INTO</code>), the column will have default value instead of expected <code class="notranslate">NULL</code>.</p>
<p dir="auto">The issue is the same using both <code class="notranslate">postgresql</code> and <code class="notranslate">in-memory sqlite</code>.</p>
<p dir="auto">Please see the script below reproducing the issue, thank you.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import BIGINT, VARCHAR


CONN_STR = &quot;sqlite:///:memory:&quot;
Base = declarative_base()


class Table(Base):
    __tablename__ = &quot;table&quot;

    id = Column(BIGINT, primary_key=True)
    field = Column(VARCHAR, default=&quot;placeholder&quot;)


def merge(session, row):
    session.begin()
    try:
        session.merge(row)
        session.commit()
    except:
        session.rollback()
        raise


def select(session, table, **criteria):
    return session.query(table).filter_by(**criteria).all()


def delete(session, table, **criteria):
    return session.query(table).filter_by(**criteria).delete()


def main():
    engine = create_engine(CONN_STR, echo=False)
    Base.metadata.create_all(engine)  # create table
    session = scoped_session(sessionmaker(bind=engine, autocommit=True))()

    data_null = {
        &quot;id&quot;: 1,
        &quot;field&quot;: None
    }
    data_value = {
        &quot;id&quot;: 2,
        &quot;field&quot;: &quot;value&quot;
    }

    # cleanup
    delete(session=session, table=Table, id=data_null[&quot;id&quot;])
    delete(session=session, table=Table, id=data_value[&quot;id&quot;])

    merge(session=session, row=Table(**data_null))
    print(select(session=session, table=Table, id=data_null[&quot;id&quot;])[0].field)  # &quot;placeholder&quot; ???
    merge(session=session, row=Table(**data_null))
    print(select(session=session, table=Table, id=data_null[&quot;id&quot;])[0].field)  # None

    merge(session=session, row=Table(**data_value))
    print(select(session=session, table=Table, id=data_value[&quot;id&quot;])[0].field)  # &quot;value&quot;
    merge(session=session, row=Table(**data_value))
    print(select(session=session, table=Table, id=data_value[&quot;id&quot;])[0].field)  # &quot;value&quot;


if __name__ == &quot;__main__&quot;:
    main()"><pre class="notranslate"><code class="notranslate">from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import BIGINT, VARCHAR


CONN_STR = "sqlite:///:memory:"
Base = declarative_base()


class Table(Base):
    __tablename__ = "table"

    id = Column(BIGINT, primary_key=True)
    field = Column(VARCHAR, default="placeholder")


def merge(session, row):
    session.begin()
    try:
        session.merge(row)
        session.commit()
    except:
        session.rollback()
        raise


def select(session, table, **criteria):
    return session.query(table).filter_by(**criteria).all()


def delete(session, table, **criteria):
    return session.query(table).filter_by(**criteria).delete()


def main():
    engine = create_engine(CONN_STR, echo=False)
    Base.metadata.create_all(engine)  # create table
    session = scoped_session(sessionmaker(bind=engine, autocommit=True))()

    data_null = {
        "id": 1,
        "field": None
    }
    data_value = {
        "id": 2,
        "field": "value"
    }

    # cleanup
    delete(session=session, table=Table, id=data_null["id"])
    delete(session=session, table=Table, id=data_value["id"])

    merge(session=session, row=Table(**data_null))
    print(select(session=session, table=Table, id=data_null["id"])[0].field)  # "placeholder" ???
    merge(session=session, row=Table(**data_null))
    print(select(session=session, table=Table, id=data_null["id"])[0].field)  # None

    merge(session=session, row=Table(**data_value))
    print(select(session=session, table=Table, id=data_value["id"])[0].field)  # "value"
    merge(session=session, row=Table(**data_value))
    print(select(session=session, table=Table, id=data_value["id"])[0].field)  # "value"


if __name__ == "__main__":
    main()
</code></pre></div>
<p dir="auto">Environment:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="python version - 3.6.3
SQLAlchemy version - 1.2.8"><pre class="notranslate"><code class="notranslate">python version - 3.6.3
SQLAlchemy version - 1.2.8
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Oleg Pidsadnyi (<a href="https://github.com/olegp">@olegp</a>)</strong></p>
<p dir="auto">Mike, I've encountered a behavior that is a bit unexpected:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class Model(Base):
    a = Column(Integer, nullable=True, default=1)

m = Model()
m.a = None
session.add(m)

In: print m.a
Out: None

session.commit()

In: print m.a
Out: 1
"><pre class="notranslate"><code class="notranslate">class Model(Base):
    a = Column(Integer, nullable=True, default=1)

m = Model()
m.a = None
session.add(m)

In: print m.a
Out: None

session.commit()

In: print m.a
Out: 1

</code></pre></div>
<p dir="auto">None is expected :(</p>
<p dir="auto">I understand that the configuration is silly, but this is the existing code. Setting None to the column doesn't cancel the default and the default thinks that the column wasn't set, so it uses the default value in the end.</p>