We are trying to changing the data type of the column, but for every time triggering "column "parental_path" cannot be cast automatically to type tsvector".
ALTER TABLE SCHEMA.TABLE ALTER COLUMN COLUMN_NAME TYPE tsvector;
ERROR: column "parental_path" cannot be cast automatically to type tsvector HINT: You might need to specify "USING parental_path::tsvector".
Q). Are we have any automatically changing mechanism for this type of conversion?.
Apparently not, otherwise the system would have used it...
Even after changing, getting below error.
ALTER TABLE SCHEMA.TABLE ALTER COLUMN COLUMN_NAME TYPE tsvector; ERROR: word is too long (2087 bytes, max 2046 bytes)
Q)Suggest, how to avoid these situations?.
So what single word in your document is more than 2k in size?
You've hit a documented limit to the data type you are using.