[BUG]: DatabaseErrorException: ERROR: column "due_date" is of type timestamp without time zone but expression is of type text; Hint: You will need to rewrite or cast the expression.

[zackperdue]

What version of drizzle-orm are you using?

0.30.9

What version of drizzle-kit are you using?

0.20.17

Describe the Bug

I'm using pg adapter and RDSDataClient

When inserting a new record with a timestamp column, the query fails with the error:

DatabaseErrorException: ERROR: column "due_date" is of type timestamp without time zone but expression is of type text; Hint: You will need to rewrite or cast the expression.

Here is my schema with the timestamp column type:

Here is where I'm trying to insert the record:

Expected behavior

I expect the record to be saved with a properly formatted timestamp and not throw an error.

Environment & setup

local

[zackperdue]

I've tried all permutations of the configuration, mode string, mode date, precision 3, with and without timezone, passing a date object and passing a string. Nothing seems to work.

[overlogic]

Looks like it is a duplicate of this bug #2097

[Steve2955]

Looks like it is a duplicate of this bug #2097

I also think so, they are very much related. Possible also even #1934. Sadly both issues are already closed, so for now it's probably worth keeping this open for visibility. I'm also facing a lot of similar issues with the typings in AWS Data Api queries using the latest versions of drizzle.

The issue seems to boil down to mergeQueries where the typings are set. Below is my proposed solution for it, which I also commented on the other issue.

Unfortunately, I'm lacking the ability to properly implement tests for this to create an PR here. It would be awesome if anyone of you could assist here.

function mergeQueries(queries: QueryWithTypings[]): QueryWithTypings {
	const result: QueryWithTypings = { sql: '', params: [] };
	for (const query of queries) {
		result.sql += query.sql;
		result.params.push(...query.params);
		if (query.typings?.length) {
			if (!result.typings) {
                                // setting an array of "none" for possible prior params 
                                // without typing resolves the issue for me:
				result.typings = new Array(result.params.length - query.params.length).fill("none");
			}
			result.typings.push(...query.typings);
		}
	}
	return result;
}

[Steve2955]

@zackperdue A possible workaround for you might be this:

{
  //...
  createdAt: sql`cast(${dayjs().utc().toDate()} as timestamp)`),
  //...
}