column is of type date but expression is of type text

So, I was trying this and it resulted in an error:

column \"date_of_birth\" is of type date but expression is of type text

column \"date_of_birth\" is of type date but expression is of type text

It happened when I tried updating multiple columns of multiple rows of a table. It doesn't happen when multiple columns of a single row are being updated. What causes this? I asked another person and they said that it's not necessarily a postgres issue and probably has somethi to do with drizzle orm.

Drizzle ORM - Update many with different values for each row

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

3 Replies

Angelelz•2mo ago

Without seeing the code it's impossible to see if there's something wrong

adrtivvOP•2mo ago

await db.update(users).set({ dateOfBirth: null }).where(eq(users.id, id)).returning();

{
    sql: `
        update
          "users" 
        set
          "date_of_birth" = $1 
        where
          "users"."id" = $2 returning "date_of_birth", "id"
          `,
    params: [null, "01930105-b607-7f81-ada2-d5ea49b393f3"],
}

await db.update(users).set({ dateOfBirth: null }).where(eq(users.id, id)).returning();

{
    sql: `
        update
          "users" 
        set
          "date_of_birth" = $1 
        where
          "users"."id" = $2 returning "date_of_birth", "id"
          `,
    params: [null, "01930105-b607-7f81-ada2-d5ea49b393f3"],
}

await db.update(users).set({
 dateOfBirth: sql`(case when ${users.id} = ${id} then ${null} end)`
}).where(inArray(users.id, [id])).returning();

{
    sql: `
        update
          "users" 
        set
          "date_of_birth" = 
          (
            case
              when
                "users"."id" = $1 
              then
                $2 
            end
          )
        where
          "users"."id" in 
          (
            $3
          )
          returning "date_of_birth", "id"
          `,
    params: [
        "01930105-b607-7f81-ada2-d5ea49b393f3",
        null,
        "01930105-b607-7f81-ada2-d5ea49b393f3",
    ],
}

await db.update(users).set({
 dateOfBirth: sql`(case when ${users.id} = ${id} then ${null} end)`
}).where(inArray(users.id, [id])).returning();

{
    sql: `
        update
          "users" 
        set
          "date_of_birth" = 
          (
            case
              when
                "users"."id" = $1 
              then
                $2 
            end
          )
        where
          "users"."id" in 
          (
            $3
          )
          returning "date_of_birth", "id"
          `,
    params: [
        "01930105-b607-7f81-ada2-d5ea49b393f3",
        null,
        "01930105-b607-7f81-ada2-d5ea49b393f3",
    ],
}

Both update operations have the same functionality, but in the second case I get column \"date_of_birth\" is of type date but expression is of type text error.

Angelelz•2mo ago

Try not passing null as parameter Just write null in the sql operator Also, try putting an else condition to your case statement

Gaming

Programming

column is of type date but expression is of type text