Re: Failure to create GiST on ltree column

пн, 25 мая 2020 г. в 18:25, Justin Pryzby <pryzby(at)telsasoft(dot)com>:

> I wonder if/how that fails if you create the index before adding data:
>
> CREATE TABLE test_path(path ltree);
> CREATE INDEX ON test_path USING GIST(path);
> INSERT INTO test_path SELECT * FROM comments.mp_comments;
>
> Does that fail on a particular row ?
>
> How many paths do you have and how long? How big is the table?
>

Yes, it fails.

I got permission and created a partial dump of the data with:
CREATE TABLE lc AS SELECT id, path FROM comments.mp_comments WHERE
length(path::text)>=500;

Attached. It is reproduces the error I get. One needs to create ltree
extension first.

I understand, that issue most likely comes from the length of the ltree
data stored in the columns.
But error is a bit misleading…

--
Victor Yegorov