setting server_default in sqlalchemy fails

Question

this is the stuff that I want SQLAlchemy to do:

blesk_dev=# alter table address add column reg_at timestamp without time zone default (now() at time zone 'utc');
ALTER TABLE

that is, I want to set a default UTC time for a column. In pure psql, as seen above, this succeeds.

This is what it's supposed to look like in SQLAlchemy code:

reg_at = db.Column(db.DateTime, server_default="(now() at time zone 'utc')")

But when I try to migrate I get this:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "utc"
LINE 1: ...STAMP WITHOUT TIME ZONE DEFAULT '(now() at time zone 'utc')'
                                                                 ^
 "ALTER TABLE address ADD COLUMN reg_at TIMESTAMP WITHOUT TIME ZONE DEFAULT '(now() at time zone 'utc')'" {}

So there's an issue with single/double quotes I presume.

I tried

"(now() at time zone \'utc\')"

but this gets me the same error.

I then swapped the quotes:

'(now() at time zone "utc")'
'(now() at time zone \"utc\")'

to no avail:

sqlalchemy.exc.DataError: (DataError) invalid input syntax for type timestamp: "(now() at time zone "utc")"
 'ALTER TABLE address ADD COLUMN reg_at TIMESTAMP WITHOUT TIME ZONE DEFAULT \'(now() at time zone "utc")\''

How do I work around this?

architectonic · Accepted Answer · 2015-09-21 10:56:26Z

8

Consider using the text function of sqlalchemy:

from sqlalchemy import text
reg_at = db.Column(db.DateTime, server_default=text('(now() at time zone "utc")'))

answered Sep 21, 2015 at 10:56

architectonic

3,1594 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dannyboy Over a year ago

text('(now() at time zone "utc")')) - does this work with postgres? I dont believe so. Error creating tables: (psycopg2.errors.FeatureNotSupported) cannot use column reference in DEFAULT expression LINE 6: ...ESTAMP WITH TIME ZONE DEFAULT (now() at time zone "utc") NOT

Collectives™ on Stack Overflow

setting server_default in sqlalchemy fails

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related