Peewee: postgresql DEFAULT now()

[mmongeon-aa]

I'm trying to create a table in PostgreSQL that uses timestamp with time zone field default now()

from playhouse.postgres_ext import *
from peewee import *
from playhouse.csv_loader import load_csv

db = PostgresqlExtDatabase('mydb', host='127.0.0.1', user='user', password='pass')

class BaseModel(Model):
    class Meta:
        database = db

class my_table(BaseModel):
    rpt_grp = TextField()
    rpt_int_grp = TextField(null=True)
    event_name = TextField()
    form_id = TextField()
    start_ts = DateField()
    end_ts = DateField(null=True)
    ts_insert = DateTimeTZField(default='now()')
    ts_update = DateTimeTZField(default='now()')

db.connect() 
my_table.create_table()

This creates a table successfully with the following DDL:

CREATE TABLE my_table
(
  id serial NOT NULL,
  rpt_grp text NOT NULL,
  rpt_int_grp text,
  event_name text NOT NULL,
  form_id text NOT NULL,
  start_ts date,
  end_ts date,
  ts_insert timestamp with time zone NOT NULL,
  ts_update timestamp with time zone NOT NULL,
  CONSTRAINT my_table_pkey PRIMARY KEY (id)
)

What i really need peewee to do is create a table that returns this:

CREATE TABLE my_table_now
(
  id serial NOT NULL,
  rpt_grp text NOT NULL,
  rpt_int_grp text,
  event_name text NOT NULL,
  form_id text NOT NULL,
  start_ts date,
  end_ts date,
  ts_insert timestamp with time zone NOT NULL DEFAULT now(),
  ts_update timestamp with time zone NOT NULL DEFAULT now()
)

[coleifer]

Peewee does not support database defaults out of the box. You can subclass playhouse.postgres_ext.DateTimeTZField and override the __ddl__ method, however:

class DefaultNowDateTimeTZField(DateTimeTZField):
    def __ddl__(self, column_type):
        ddl = super(DefaultNowDateTimeTZField, self).__ddl__(column_type)
        ddl.append(SQL('DEFAULT now()'))
        return ddl

[coleifer]

Peewee supports server-side constraints as of a while ago but I apologize as I never followed through updating this ticket. If you are still curious how to do this, you can:

timestamp = DateTimeField(constraints=[SQL('DEFAULT now()')])

[whisller]

@coleifer it doesn't look like it's working with SqliteDatabase

self = <peewee.SqliteDatabase object at 0x10522fd68>
sql = 'CREATE TABLE "my_table" ("id" INTEGER NOT NULL PRIMARY KEY, "asin" VARCHAR(255) NOT NULL, "cat... "updated_at" DATETIME NOT NULL DEFAULT now(), FOREIGN KEY ("category_id") REFERENCES "category" ("id"))'
params = [], require_commit = True

    def execute_sql(self, sql, params=None, require_commit=True):
        logger.debug((sql, params))
        with self.exception_wrapper:
            cursor = self.get_cursor()
            try:
>               cursor.execute(sql, params or ())
E               peewee.OperationalError: near "(": syntax error

venv/lib/python3.6/site-packages/peewee.py:3758: OperationalError

[coleifer]

In SQLite the expression is different:

timestamp DATETIME DEFAULT CURRENT_TIMESTAMP