Field default timestamp set to table creation time instead of row creation time

Question

Using SQLAlchemy, alembic and postgres, when trying to set a column to the row creation time what I finally get is a field that defaults to the time when the table itself was created, instead of the time when the row was created.

Model code:

datetime = sa.Column(sa.DateTime, nullable=False, server_default=func.now())

Alembic translates it to:

sa.Column('datetime', sa.DateTime(), server_default='now()', nullable=False),

And the column in Postgres:

datetime  | timestamp without time zone | not null default '2013-06-24 11:28:14.930524'::timestamp without time zone

What should I do so that the default is the row creation time?

Answer 1

Aha, worked it out-- seems you need to tell the server_default command if you're sending in some SQL that needs to be executed on the DBMS itself:

from sqlalchemy import text

class Test(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.DateTime, server_default=text('now()'))

That generates:

CREATE TABLE test (
    id SERIAL NOT NULL, 
    created TIMESTAMP WITHOUT TIME ZONE DEFAULT now(), 
    PRIMARY KEY (id)
)