Drizzle TeamDT
Drizzle Team•2y ago
Rorsch

Altering existing table with Timestamp column to Timestamp with Timezone in Postgres using Drizzle

I have an existing table with columns such as created_at with data type of timestamp. I want to update the column, so that every single timestamp in my database will be timestamp with timezone. I tried modifying db schema from JS by changing
  createdAt: timestamp("created_at")
    .defaultNow()
    .notNull(),

to
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),

When trying to push the changes, drizzle-kit warned me that my data will be lost. 
...

ALTER TABLE "accounting_transaction" ALTER COLUMN "created_at" SET DATA TYPE timestamp with time zone;
truncate table "accounting_transaction" cascade;

...

 Warning  Found data-loss statements:
· You're about to change created_at column type from timestamp to timestamp with time zone with 42 items

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
[x] All changes were aborted

What strategy that I can use to achieve this?
Was this page helpful?