Numeric codec doesn't seem to work

[gvolpe]

I get this exception when trying to have a column "total" of type numeric:

skunk.exception.PostgresErrorException: Column "total" is of type numeric but expression is of type character varying.

After a bit of digging I found this:

https://stackoverflow.com/questions/45873514/postgresql-hint-you-will-need-to-rewrite-or-cast-the-expression-column-state

Apparently the SQL statement should specify the type when using setString. Something like:

INSERT INTO foo (bar, total)
VALUES (?, ?::numeric)

Have you stumbled upon this issue? If you're okay with it I can try to fix it and send a PR :)

[tpolecat]

The types of all parameters are set when I prepare the statement, and it's all checked before execution. How are you provoking this?

[gvolpe]

I have the following table:

CREATE TABLE orders (
  uuid VARCHAR PRIMARY KEY,
  user_id VARCHAR UNIQUE NOT NULL,
  payment_id VARCHAR UNIQUE NOT NULL,
  items VARCHAR NOT NULL,
  total NUMERIC
);

And this command (o.total.value is of type BigDecimal):

val insertOrder: Command[UserId ~ Order] =
  sql"""
      INSERT INTO orders
      VALUES ($varchar, $varchar, $varchar, $varchar, $numeric)
     """.command.contramap {
    case id ~ o =>
      o.id.value.toString ~ id.value.toString ~ o.paymentId.value.toString ~ o.items.asJson.noSpaces ~ o.total.value
  }

This is how the command is executed (not sure if relevant):

  def create(
      userId: UserId,
      paymentId: PaymentId,
      items: List[CartItem],
      total: USD
  ): F[OrderId] =
    sessionPool.use { session =>
      session.prepare(insertOrder).use { cmd =>
        GenUUID[F].make[OrderId].flatMap { id =>
          val itMap = items.map(x => x.item.uuid -> x.quantity).toMap
          val order = Order(id, paymentId, itMap, total)
          cmd.execute(userId ~ order).as(id)
        }
      }
    }

The USD type simply wraps a BigDecimal, which is the type of the total column.

Here is the error being logged in the Postgres server:

2019-11-04 21:48:38.883 UTC [31] ERROR:  column "total" is of type numeric but expression is of type character varying at character 61
2019-11-04 21:48:38.883 UTC [31] HINT:  You will need to rewrite or cast the expression.
2019-11-04 21:48:38.883 UTC [31] STATEMENT:  
	        INSERT INTO orders
	        VALUES ($1, $1, $1, $1, $1)

Anything I'm doing obviously wrong? 😄

[gvolpe]

I think the bug might have been introduced when inserting values in the following way was fixed:

INSERT INTO foo
VALUES ($codec)

If there's any other error this is what I see in the Postgres logs (before it was all $1s):

                INSERT INTO orders
	        VALUES ($1, $2, $3, $4, $5)

By using the syntax INSERT INTO foo VALUES ($codec) I got it working.

[gvolpe]

Summarizing

This works:

val codec: Codec[UserId ~ Order] =
  (varchar ~ varchar ~ varchar ~ varchar ~ numeric).imap {
    case o ~ u ~ p ~ i ~ t =>
      ju.UUID.fromString(u).coerce[UserId] ~
        Order(
          ju.UUID.fromString(o).coerce[OrderId],
          ju.UUID.fromString(p).coerce[PaymentId],
          decode[Map[ItemId, Quantity]](i).getOrElse(Map.empty),
          t.coerce[USD]
        )
  } {
    case id ~ o =>
      o.id.value.toString ~ id.value.toString ~ o.paymentId.value.toString ~ o.items.asJson.noSpaces ~ o.total.value
  }

val insertOrder: Command[UserId ~ Order] =
  sql"""
      INSERT INTO orders
      VALUES ($codec)
     """.command

---

This doesn't:

val insertItem: Command[ItemId ~ CreateItem] =
  sql"""
      INSERT INTO items
      VALUES ($varchar, $varchar, $varchar, $numeric, $varchar, $varchar)
     """.command.contramap {
    case id ~ i =>
      id.value.toString ~ i.name.value ~ i.description.value ~ i.price.value ~ i.brandId.value.toString ~ i.categoryId.value.toString
  }

[tpolecat]

Yeah, the bug is that it's all $1. I'll have a look.

[gvolpe]

Thanks!

[gvolpe]

I think adding the following command to CommandTest should reveal the issue:

val insertCity: Command[City] =
  sql"""
       INSERT INTO city
       VALUES ($int4, $varchar, $bpchar(3), $varchar, $int4)
     """.command.contramap { 
          case c => c.id ~ c.name ~ c.code ~ c.district ~ c.pop 
        }

[tpolecat]

This fix is available in 0.0.4+25-afe55d7f-SNAPSHOT which is being built right now.

[gvolpe]

Wow, that was quick, thanks! 🚀