1

I had DB Vertica 8.0. Create schema WAREHOUSE with table include field by type LONG VARCHAR. Now I tried to execude SELECT for example

SELECT * FROM WAREHOUSE.ALL_EVENTS a 
WHERE 
a.original_data like '%d963%'

returned error

SQL Error [4286] [42883]: [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown
  [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown
    com.vertica.util.ServerException: [Vertica][VJDBC](4286) ERROR: Operator does not exist: long varchar ~~ unknown

In Oracle I used dbms_lob package for CLOB fields.

Vertica have simular package for LONG VARCHAR types?

How "LIKE" by LONG VARCHAR?

  • 1
    How long are the data in that column? – ypercubeᵀᴹ Oct 20 '17 at 18:55
  • length 1 048 576 – Nikolay Baranenko Oct 20 '17 at 18:56
  • 1
    I don't think you can, I added an answer. – ypercubeᵀᴹ Oct 20 '17 at 18:59
  • 1
    Is 1048576 the length of the longest string or how the column is defined? – ypercubeᵀᴹ Oct 20 '17 at 19:06
  • this length showed DB SQL Developer - DBeaver. type LON VARCHAR selected because in Oracle was type - CLOB – Nikolay Baranenko Oct 20 '17 at 19:11
2

Vertica documentation about LONG datatypes, in either version 7.1 or 8.0 states:

Notes

For optimal performance of LONG data types, HP Vertica recommends that you:

  • Use the LONG data types as storage only containers; HP Vertica does not support operations on their content.
  • Use the VARBINARY and VARCHAR data types, instead of their LONG counterparts, whenever possible. The VARBINARY and VARCHAR data types are more flexible and have a wider range of operations.

VARCHAR datatype supports up to 65K bytes long strings. You could use that instead, if the data are not longer.

0

You can use REGEXP_LIKE instead of LIKE. Refer to the Reference Page and this Stackoverflow answer of similar question. However you may not use % or _ simple wildcard which supported by LIKE operator. In order to find more complex case, you should know about Regular Expressions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.