Question 219691 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Entity to support multiple dialects

I have an SQLAlchemy entity written for PostgreSQL dialect and it uses server_default=func.clock_timestamp():

row_created = sa.Column('row_created_', sa.DateTime(timezone=True), server_default=func.clock_timestamp(), nullable=False)

I also need to use it with SQLite, which throws an error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown function: clock_timestamp()

How do I make this work with SQLAlchemy? Define the server_default only for SQLite? Conditionally add entire column only for specific dialect? Somehow make clock_timestamp() known to SQLite (I don’t care what the function will do, as long as it does not fail)?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I got a tip from a colleague of mine, which I ended up using and it works fine for both postgresql as well as sqlite.

    server_default=func.now()

    afaik it is the same as func.clock_timestamp(), but clock_timestamp() is postgresql-specific and now() is supoported by several DBs.


  2. 0

    You can use: text("CURRENT_TIMESTAMP") for the server default:

    class TimeStamp(Base):
    __tablename__ = "timestamp"

    id: Mapped[int] = mapped_column(
        primary_key=True,
    )

    datetime_with_tz: Mapped[datetime] = mapped_column(
        types.DateTime(timezone=True),
        server_default=text("CURRENT_TIMESTAMP"),
    )

    This will create a table with the following SQL:

    PostgresSQL:

    CREATE TABLE timestamp (
        id SERIAL NOT NULL,
        datetime_with_tz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (id)
)

    SQLite:

    CREATE TABLE timestamp (
        id INTEGER NOT NULL,
        datetime_with_tz DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (id)
)

    If you must use clock_timestamp() on PostgreSQL, you can set server default
    to:

    server_default=text("CURRENT_TIMESTAMP") if use_sqlite else text("clock_timestamp()"),

    postgres

    CREATE TABLE timestamp (
        id SERIAL NOT NULL,
        datetime_with_tz TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
        PRIMARY KEY (id)
)

    sqlite

    CREATE TABLE timestamp (
        id INTEGER NOT NULL,
        datetime_with_tz DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (id)
)

    You can also use "default" for it to work on both databases.

    timestamp_auto = Annotated[
    datetime.datetime,
    mapped_column(
        default=datetime.datetime.now,
    ),
]


class TimeStamp(Base):
    __tablename__ = "timestamp"

    id: Mapped[int] = mapped_column(
        primary_key=True,
    )

    datetime_with_tz: Mapped[timestamp_auto] = mapped_column(
        types.DateTime(timezone=True),
    )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search