[SOLVED] why when I execute a select query on a view in vertica, do I get "Length for type varchar cannot exceed 65000"?

why when I execute a select query on a view in vertica, do I get "Length for type varchar cannot exceed 65000"?

I have a flextable in vertica and am trying to create a view based on it.

Here's the create view statement:

CREATE VIEW testview AS
    SELECT
            COALESCE (
                "USER_ID",
                "userId",
                "SM.actor.id",
                "SM.participant.userId",
                "SM.userId",
                "SM.uid",
                "SM_C.userId",
                "SM.id" )::varchar AS userid
    FROM
        flex_table_test
    WHERE "SM.verb" is not null

I can create the view successfully, but when I execute something like:

SELECT * FROM testview LIMIT 10;

I get:

ERROR 3852: Length for type varchar cannot exceed 65000

If I execute just the select part of the view by itself, it works fine. I tried casting the output fields, but it doesn't seem to make much of a difference.

Solution

By default, data in Flex tables are stored as LONG VARBINARY which has a default raw size of 130000. Since this data is longer than the maximum length for VARCHAR (65000), you need to either truncate the result of your COALESCE or try casting it as a ::long varchar. If the latter works, be aware that Vertica does not support operations on this content type.