0

with a very simple table like this

create table matpath (
    obj varchar primary key,
    path ltree
);

I'm trying to get run the following query :

select path || obj from matpath;

but it doesn't work, I get syntax error at position 8

It works if I use a literal text

select path || 'sometext' from matpath;

I suspect I need to cast obj to something but I can't figure out what it need to be.

I tried varchar, text, ltree

EDIT: I'm on postgresql 11.1

BlueMagma
  • 2,392
  • 1
  • 22
  • 46

1 Answers1

1

By the look of the error message posted in the comments you appear to have an invalid character in your obj column, to find it try

SELECT *
FROM matpath
WHERE obj !~ '^[a-zA-Z0-9_.]*$' -- find invalid characters
OR octet_length(path::varchar || obj) > 256 -- find keys that are potentially too long

Ltree can only contain characters A-Za-z0-9_ and . and must not exceed 256 bytes.

https://www.postgresql.org/docs/current/ltree.html#id-1.11.7.30.4

Lucas
  • 600
  • 3
  • 9
  • If I'm not mistaken, a label path can contain up to "65535 labels," it's an individual label that cannot be more than 256 characters. (See https://www.postgresql.org/docs/current/ltree.html.) – JMB Nov 30 '22 at 17:28