Drizzle Team•4mo ago

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(),

  createdAt: timestamp("created_at")
    .defaultNow()
    .notNull(),

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

  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

...

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?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

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