[BUG]: timstamp space in postgresql

[chtibizoux]

What version of drizzle-orm are you using?

0.28.5

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

I'm using postgresql and i have this simple table:

export const users = pgTable('users', {
    id: text('id')
        .$defaultFn(() => createId())
        .primaryKey(),
    name: text('name').unique().notNull(),
    email: varchar('email', { length: 320 }).unique().notNull(),
    password: text('password'),
    createdAt: timestamp('created_at', { precision: 3 }).defaultNow().notNull(),
});

Expected behavior

when i run npx drizzle-kit push:pg drizzle truncate my table because of a space.

You're about to change created_at column type from timestamp(3) to timestamp (3) with 1 items

Environment & setup

No response

[djarran]

Bug appears to be caused in the following function when getting a new snapshot:

preparePgDbPushSnapshot = async (prev, schemaPath, schemaFilter = ["public"]) => {
      const serialized = await serializePg(schemaPath, schemaFilter);
      const id = (0, import_crypto.randomUUID)();
      const idPrev = prev.id;
      const { version: version2, dialect: dialect6, ...rest } = serialized;
      const result = { version: version2, dialect: dialect6, id, prevId: idPrev, ...rest };

      return { prev, cur: result };
    }

The values from the specific column causing the issue (fulfillment_date) had the following different values in prev and serialized respectively when printing to the console:

timestamp(6) with time zone
timestamp (6) with time zone

When the two json objects are passed to applyJsonDiff,

function applyJsonDiff(json1, json2) {
  json1 = JSON.parse(JSON.stringify(json1));
  json2 = JSON.parse(JSON.stringify(json2));
  console.log('latest search here')
  const rawDiff = (0, import_json_diff.diff)(json1, json2);
  const difference = rawDiff;
  const tableToSchema = Object.entries(json2.tables).reduce((res, it) => {
    res[it[0]] = it[1].schema;
    return res;
  }, {});
  if (!difference)
    return {};
  difference.tables = difference.tables ?? {};
  ...
}

difference.tables is populated

{
  catering_orders: { 
     columns: { 
          fulfillment_date:  
              type: {
                 __old: 'timestamp(6) with time zone',
                 __new: 'timestamp (6) with time zone'
              }
      }
  }
}

Not best practice, but to skip this issue I just replace the column types to be the same in preparePgDbPushSnapshot:

serialized.tables.<table_name>.columns.<date_column>.type = prev.tables.<table_name>.columns.<date_column>.type;

[viciousnemesis]

Bumping, as I am also getting this issue

[peter-skillmasters]

This bug makes the push:pg command only useful in non-live environments because you end up having to truncate every table with a timestamp each time it is run.

[srigi]

I had the exact same issue with TIMESTAMP columnt. The simple workaroud is NOT to define custom precision.

[swarajbachu]

still have the same issue, I hope it gets fixed @AndriiSherman

· You're about to change createdAt column type from timestamp(6) with time zone to timestamp (6) with time zone with 2 items · You're about to change updatedAt column type from timestamp(6) with time zone to timestamp (6) with time zone with 2 items · You're about to change ls_current_period_end column type from timestamp(6) with time zone to timestamp (6) with time zone with 2 items

[Aexylus]

Run into this today, annoying little bug. Here's my dumb solution that works.

Add this to the preparePgDbPushSnapshot fn in bin.cjs in drizzle-kit

Object.keys(result.tables).forEach((t) => {
  Object.keys(result.tables[t].columns).forEach((c) => {
    result.tables[t].columns[c].type = result.tables[t].columns[c]
    .type.replace(/^timestamp \(/, 'timestamp(');
  });
});