Cast column type jsonb to varchar or text

1

I need convert field of type jsonb to varchar or text.
I tried to find something in the doc or other forums, but I didn’t find the solution

   [Error Id: 31913ae5-ee76-4e13-99a8-f106b3c800c3 on ip-172-31-5-186.ec2.internal:31010]

  (org.postgresql.util.PSQLException) ERROR: collations are not supported by type jsonb
  Position: 528
    org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2440
    org.postgresql.core.v3.QueryExecutorImpl.processResults():2183
    org.postgresql.core.v3.QueryExecutorImpl.execute():308
    org.postgresql.jdbc.PgStatement.executeInternal():441
    org.postgresql.jdbc.PgStatement.execute():365
    org.postgresql.jdbc.PgStatement.executeWithFlags():307
    org.postgresql.jdbc.PgStatement.executeCachedSql():293
    org.postgresql.jdbc.PgStatement.executeWithFlags():270
    org.postgresql.jdbc.PgStatement.executeQuery():224
    org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
    org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
    com.dremio.exec.store.jdbc.JdbcRecordReader.setup():203
    com.dremio.exec.store.CoercionReader.setup():150
    com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():219
    com.dremio.sabot.op.scan.ScanOperator.setupReader():192
    com.dremio.sabot.op.scan.ScanOperator.setup():178
    com.dremio.sabot.driver.SmartOp$SmartProducer.setup():563
    com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
    com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
    com.dremio.sabot.driver.SmartOp$SmartProducer.accept():533
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.Pipeline.setup():68
    com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():388
    com.dremio.sabot.exec.fragment.FragmentExecutor.run():270
    com.dremio.sabot.exec.fragment.FragmentExecutor.access$1200():92
    com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():674
    com.dremio.sabot.task.AsyncTaskWrapper.run():104
    com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():226
    com.dremio.sabot.task.slicing.SlicingThread.run():156
2

@idoor88

What version of Dremio is this? Can you please try this on 4.1.8?

Thanks
@balaji.ramaswamy

3

Hi,
I’m using exactly the version 4.1.8
Build Time: 12/03/2020

Thanks

4

Hi @balaji.ramaswamy. Is there any way to resolve this?

Thanks!

5

@l-jhon,

This should be addressed in our upcoming 4.2 release. But currently not a workaround, unfortunately.

6

Ok, I’ll wait.

Thanks, @ben!