Postgres JSON and JSONB types not supported

[luchesar]

Postgres JSON and JSONB types not supported

[griffio]

🤔 I will try and create a PR to test out a fix 🛠️

There are at-least three ways that could be implemented to set the parameter and type in JDBC to keep the generated data class field as a String type

• ?::jsonb
• PGobject - type "jsonb"
• setObject(index, obj, Types.OTHER)

setObject with types.OTHER appears to be the most viable in the current code base.
💡 A new support method bindObjectOther in

sqldelight/drivers/jdbc-driver/src/main/kotlin/app/cash/sqldelight/driver/jdbc/JdbcDriver.kt

Line 256 in 23cf21f

fun bindObject(index: Int, obj: Any?) {

would be needed and a cursor mapping from a new JSON enum type to a String

 JSON -> "$cursorName.getString($columnIndex)"

in

sqldelight/dialects/postgresql/src/main/kotlin/app/cash/sqldelight/dialects/postgresql/PostgreSqlType.kt

Lines 24 to 48 in 23cf21f

	override fun prepareStatementBinder(columnIndex: CodeBlock, value: CodeBlock): CodeBlock {
	return CodeBlock.builder()
	.add(
	when (this) {
	SMALL_INT -> "bindShort"
	INTEGER -> "bindInt"
	BIG_INT -> "bindLong"
	DATE, TIME, TIMESTAMP, TIMESTAMP_TIMEZONE, INTERVAL, UUID -> "bindObject"
	NUMERIC -> "bindBigDecimal"
	},
	)
	.add("(%L, %L)\n", columnIndex, value)
	.build()
	}
	override fun cursorGetter(columnIndex: Int, cursorName: String): CodeBlock {
	return CodeBlock.of(
	when (this) {
	SMALL_INT -> "$cursorName.getShort($columnIndex)"
	INTEGER -> "$cursorName.getInt($columnIndex)"
	BIG_INT -> "$cursorName.getLong($columnIndex)"
	DATE, TIME, TIMESTAMP, TIMESTAMP_TIMEZONE, INTERVAL, UUID -> "$cursorName.getObject<%T>($columnIndex)"
	NUMERIC -> "$cursorName.getBigDecimal($columnIndex)"
	},
	javaType,

[OfekTeken]

@griffio I also encountered this issue, and if I remember correctly, this fixed it for now:
CAST(:jsonColumn AS JSONB)
Of course it won't work here because the OP tried to insert using VALUES ?