Sqlalchemy+Psycopg创建PostgreSQL表时DEFAULT表达式报错求助

尝试使用SQLAlchemy在PostgreSQL中创建表，代码如下：

from sqlalchemy import Column, DateTime, Float, Integer, String, func
from sqlalchemy.orm import declarative_base


Base = declarative_base()


class Test(Base):
    __tablename__ = "tests"

    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    create_date = Column(DateTime, server_default=func.sysdate())

执行建表代码：

Test.__table__.create(bind=engine, checkfirst=True)

出现如下错误：

Traceback (most recent call last):
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 747, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.FeatureNotSupported: cannot use column reference in DEFAULT expression
LINE 5:  create_date TIMESTAMP WITHOUT TIME ZONE DEFAULT sysdate,
                                                         ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/matteo/Documents/DataKind/MyProject/application/backend/src/app/db/import_data.py", line 22, in <module>
    Test.__table__.create(bind=engine, checkfirst=True)
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/sql/schema.py", line 1149, in create
    bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3220, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2427, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 670, in traverse_single
    return meth(obj, **kw)
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 958, in visit_table
    CreateTable(
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 315, in _invoke_with
    return bind.execute(self)
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
    return meth(
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py", line 181, in _execute_on_connection
    return connection._execute_ddl(
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1526, in _execute_ddl
    ret = self._execute_context(
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2325, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "/Users/matteo/Documents/DataKind/MyProject/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 747, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.NotSupportedError: (psycopg2.errors.FeatureNotSupported) cannot use column reference in DEFAULT expression
LINE 5:  create_date TIMESTAMP WITHOUT TIME ZONE DEFAULT sysdate,
                                                         ^

[SQL:
CREATE TABLE tests (
    id SERIAL NOT NULL,
    name VARCHAR,
    create_date TIMESTAMP WITHOUT TIME ZONE DEFAULT sysdate,
    PRIMARY KEY (id),
    UNIQUE (name)
)

]

报错原因是PostgreSQL不支持将sysdate作为列默认值——sysdate是Oracle的内置函数，PostgreSQL中没有这个标识符，会被误认为列引用，从而抛出错误。

只需将create_date字段的server_default替换为PostgreSQL支持的当前时间函数即可，有两种常用方式：

1. 使用SQLAlchemy的func.now()：

create_date = Column(DateTime, server_default=func.now())

2. 使用原生SQL的CURRENT_TIMESTAMP（需导入text）：

from sqlalchemy import text

create_date = Column(DateTime, server_default=text('CURRENT_TIMESTAMP'))

修改后重新执行建表代码，即可成功创建带有默认当前时间的create_date列。

内容的提问来源于stack exchange，提问作者user1315621